System Databases Maintenance Best Practices | SQL Server Performance Forums

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.<br /><br />But rebuild of indexes should be carried out only if the logical fragmentation of clustered index is greater than 20 %.<br /><br />I would prefer manual check by running of the script once a week and rebuild based on output.<br /><br />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.<br /><br />I would wait for SQL gurus opnions on this methodology.<br /><br /><br /> <br />/*Perform a ‘USE &lt;database name&gt;’ to select the database in which to run the script.*/<br />– Declare variables<br />SET NOCOUNT ON<br />DECLARE @tablename VARCHAR (12<img src=’/community/emoticons/emotion-11.gif’ alt=’8)’ /><br />DECLARE @execstr VARCHAR (255)<br />DECLARE @objectid INT<br />DECLARE @indexid INT<br />DECLARE @frag DECIMAL<br />DECLARE @maxfrag DECIMAL<br /><br />– Decide on the maximum fragmentation to allow<br />SELECT @maxfrag = 20.0<br /><br />– Declare cursor<br />DECLARE tables CURSOR FOR<br /> SELECT TABLE_NAME<br /> FROM INFORMATION_SCHEMA.TABLES<br /> WHERE TABLE_TYPE = ‘BASE TABLE'<br /><br />– Create the table<br />CREATE TABLE #fraglist (<br /> ObjectName CHAR (255),<br /> ObjectId INT,<br /> IndexName CHAR (255),<br /> IndexId INT,<br /> Lvl INT,<br /> CountPages INT,<br /> CountRows INT,<br /> MinRecSize INT,<br /> MaxRecSize INT,<br /> AvgRecSize INT,<br /> ForRecCount INT,<br /> Extents INT,<br /> ExtentSwitches INT,<br /> AvgFreeBytes INT,<br /> AvgPageDensity INT,<br /> ScanDensity DECIMAL,<br /> BestCount INT,<br /> ActualCount INT,<br /> LogicalFrag DECIMAL,<br /> ExtentFrag DECIMAL)<br /><br />– Open the cursor<br />OPEN tables<br /><br />– Loop through all the tables in the database<br />FETCH NEXT<br /> FROM tables<br /> INTO @tablename<br /><br />WHILE @@FETCH_STATUS = 0<br />BEGIN<br />– Do the showcontig of all indexes of the table<br /> INSERT INTO #fraglist <br /> EXEC (‘DBCC SHOWCONTIG (”’ + @tablename + ”’) <br /> WITH FAST, TABLERESULTS, ALL_INDEXES, NO_INFOMSGS’)<br /> FETCH NEXT<br /> FROM tables<br /> INTO @tablename<br />END<br /><br />– Close and deallocate the cursor<br />CLOSE tables<br />DEALLOCATE tables<br /><br /><br />– Do select to get the indexs id which have greater fragmentation 30%<br /><br /> SELECT ObjectName, ObjectId, IndexId,IndexName ,LogicalFrag<br /> FROM #fraglist<br /> WHERE LogicalFrag &gt;= @maxfrag<br /> AND INDEXPROPERTY (ObjectId, IndexName, ‘IndexDepth’) &gt; 0<br /><br />DROP TABLE #fraglist<br /><br /><br /><br /><br /><br /><br />
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
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
[<img src=’/community/emoticons/emotion-1.gif’ alt=’:)‘ />] that will not happen if you are learning about SQL Server.<br />Its more important that you are ready to share your knowledge and that helps a lot.<br /><br /><b>Satya SKJ</b><br />Microsoft SQL Server MVP<br />Writer, Contributing Editor & Moderator<br /<a target="_blank" href=http://www.SQL-Server-Performance.Com>http://www.SQL-Server-Performance.Com</a><br /><center><font color="teal"><font size="1">This posting is provided AS IS with no rights for the sake of <i>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.</i></font id="size1"></font id="teal"></center>