SQL Server Performance Forum – Threads Archive

System Databases Maintenance Best Practices

Hi all, Which Job(s) must be created to optimize the systems Database and keep them uptodate ? Our sql server heavy use Merge Replications … Is a Rebuild Index of every sysdb every week is a good idea ? or use ponctual maintenance job ? Regards

Maintianence of system DB’s can be once a week …may once in weekend when the servers are not busy. But rebuild of indexes should be carried out only if the logical fragmentation of clustered index is greater than 20 %. I would prefer manual check by running of the script once a week and rebuild based on output. Please test the script I took from BOL and modified a bit….Please not that I dont not have merge replication running on my server so please test it before you plan to use it. I would wait for SQL gurus opnions on this methodology. /*Perform a ‘USE <database name>’ to select the database in which to run the script.*/ – Declare variables SET NOCOUNT ON DECLARE @tablename VARCHAR (12<img src=’/community/emoticons/emotion-11.gif’ alt=’8)’ /> DECLARE @execstr VARCHAR (255) DECLARE @objectid INT DECLARE @indexid INT DECLARE @frag DECIMAL DECLARE @maxfrag DECIMAL – Decide on the maximum fragmentation to allow SELECT @maxfrag = 20.0 – Declare cursor DECLARE tables CURSOR FOR SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = ‘BASE TABLE' – Create the table CREATE TABLE #fraglist ( ObjectName CHAR (255), ObjectId INT, IndexName CHAR (255), IndexId INT, Lvl INT, CountPages INT, CountRows INT, MinRecSize INT, MaxRecSize INT, AvgRecSize INT, ForRecCount INT, Extents INT, ExtentSwitches INT, AvgFreeBytes INT, AvgPageDensity INT, ScanDensity DECIMAL, BestCount INT, ActualCount INT, LogicalFrag DECIMAL, ExtentFrag DECIMAL) – Open the cursor OPEN tables – Loop through all the tables in the database FETCH NEXT FROM tables INTO @tablename WHILE @@FETCH_STATUS = 0 BEGIN – Do the showcontig of all indexes of the table INSERT INTO #fraglist EXEC (‘DBCC SHOWCONTIG (”’ + @tablename + ”’) WITH FAST, TABLERESULTS, ALL_INDEXES, NO_INFOMSGS’) FETCH NEXT FROM tables INTO @tablename END – Close and deallocate the cursor CLOSE tables DEALLOCATE tables – Do select to get the indexs id which have greater fragmentation 30% SELECT ObjectName, ObjectId, IndexId,IndexName ,LogicalFrag FROM #fraglist WHERE LogicalFrag >= @maxfrag AND INDEXPROPERTY (ObjectId, IndexName, ‘IndexDepth’) > 0 DROP TABLE #fraglist

Appreciate Techbabu’s reply and agree with the specified terms.
Its best for you to choose which is less traffic time on the database and schedule the jobs. Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.

Great , thanks a lot four your supports Bests Regards

Thx Satya…for encourgement…got learnt a lot for you and others though I have not met you all personally!!! May be some years after I gain few kilos due to indeigestion and loose a lot of hair ….I will be still learning a lot to digest from you and Product lifetime. Cheers
Sat

[<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />] that will not happen if you are learning about SQL Server. Its more important that you are ready to share your knowledge and that helps a lot. Satya SKJ Microsoft SQL Server MVP Writer, Contributing Editor & Moderator http://www.SQL-Server-Performance.Com</a> <center>This posting is provided AS IS with no rights for the sake of knowledge sharing. <hr noshade size="1">The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.</center>

]]>