Is this the best maintenance plan? | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Is this the best maintenance plan?

Hi there. We have a weekly maintenance plan running for our user databases. It was created with the SSIS wizard and then tweaked a little. My question is regarding the order in which the operations are carried out, and whether or not this is the best order, or does it make any difference. Start -> Update Statistics -> Reorganise Index -> Check Database -> Shrink Database -> End We would like to try and reduce the amount of time it takes for the operations to run. Also, last week the DBCC SHRINKDATABASE operation failed with the following message. I have not seen this message before. DBCC SHRINKDATABASE: File ID 1 of database ID 9 was skipped because the file does not have enough free space to reclaim.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly. Any help or tips most appreciated.
The order of the plan is not good. 1) Reindex update statistics also. Except if you only reindex some tables but not all.
2) Shrink database remove all reindex and/or update statistics you did.
3) After reindex database increase, if you shrink database the next reindex will increase again. So, Update statistics 2 times a week and reindex on week ends only those indexes with fragmentation. Luis Martin
Moderator
SQL-Server-Performance.com All in Love is Fair
Stevie Wonder
All postings are provided “AS IS” with no warranties for accuracy.
I agree with Luis, updating statistics and rebuilding indexes is redundant. In addition, shrinking a database automatically rarely provides much benefit unless you know that your database adds and deletes a huge amount of data every day. What I try to do is to rebuild indexes once a week, check databases once a week, and only shrink a database manually if it needs it. I rarely update statistics as a separate job unless I learn from experience that it is helpful to the database. And when I do update statistics, it is on different day(s) that when I rebuild the indexes. But each database is a little different and needs unique treatment. —————————–
Brad M. McGehee, SQL Server MVP
In addition to above I would also suggest to leave the transaction log settings as is and not to shrink on regular basis, you can add more physical space to the server in order to provide the future growth of the database. Also I believe on SQL Server you left the memory settings to dynamic. Satya SKJ
Microsoft SQL Server MVP
Contributing Editor & Forums Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing.
]]>