![]() |
|
Post Reply ![]() |
Author | ||
Stephen ![]() Admin Group ![]() ![]() Joined: 21 Oct 2005 Location: Stoke on Trent Points: 1454 |
![]() ![]() ![]() ![]() ![]() Posted: 11 Jan 2007 at 11:47am |
|
If you are using a SQL database to store you WhosOn data.. and you have a reasonable amount of traffic (say have a database size more than 1GB), then you should perform regular maintenance on the database to keep the database healthy and maintain optimal performance.
This article shows how to do this for SQL Server 2005 (similar maintenance functions also exist in MySQL). If you are using SQL Server 2005 full version (not Express), then you should create a Maintenance Plan for the WhosOn DB that executes weekly (say Sunday morning). This maintenance plan should be set to Reorganize each table and the perform a Shrink database. This plan can be run whilst WhosOn is running. If you are using SQL Server 2005 Express edition then you wont be able to do this because express does not include the Maintenance plan wizard.. however you can do the same by executing some queries manually. The following queries with regorganize all the main indexes and shrink the database. These can be executed against the WhosOn database in the SQL Server Management studio:
You can run these whilst WhosOn is running. We recommend you do this once per month at least. You can also execute the following stored procedure to view the status of the WhosOn DB:
Steve |
||
![]() |
||
fabiozarvos ![]() New User ![]() Joined: 25 Nov 2007 Location: So PauloSP Points: 3 |
![]() ![]() ![]() ![]() ![]() |
|
I did not find the Visit_key2 index on Visit table. Am I missing something? |
||
![]() |
||
Daniel ![]() Admin Group ![]() ![]() Technical Director Joined: 19 Dec 2006 Location: Stoke-on-Trent Points: 881 |
![]() ![]() ![]() ![]() ![]() |
|
No - this has been removed - the correct code is now:
|
||
Daniel Tallentire
Support Parker Software |
||
![]() |
||
matrixIII ![]() Professional ![]() Joined: 21 Jul 2008 Points: 68 |
![]() ![]() ![]() ![]() ![]() |
|
Can we get this for V5? V4 script is generating some errors..
|
||
![]() |
||
Stephen ![]() Admin Group ![]() ![]() Joined: 21 Oct 2005 Location: Stoke on Trent Points: 1454 |
![]() ![]() ![]() ![]() ![]() |
|
Hi,
In V5 WhosOn reorganized indexes itself every night during the database maintenance. It first checks the Statistics of the DB and does an re-index for any indexes that are fragmented.
The V4 scripts wont work in V5 because the tables/index names are changed.
Thanks
Steve
|
||
![]() |
||
matrixIII ![]() Professional ![]() Joined: 21 Jul 2008 Points: 68 |
![]() ![]() ![]() ![]() ![]() |
|
ahh.. I see.. Didn't know that before. I guess it happens automatically without having us to set up something (dedicated server option)?
|
||
![]() |
Post Reply ![]() |
|
Tweet
|
Forum Jump | Forum Permissions ![]() You cannot post new topics in this forum You cannot reply to topics in this forum You cannot delete your posts in this forum You cannot edit your posts in this forum You cannot create polls in this forum You cannot vote in polls in this forum |