I am a new DBA to a SQL 2005 production Report server. My predecessor created a Maintenance Plan with 4 tasks: CHECKDB; SHRINKDATABASE (N'DB1',10,TRUNCATEONLY); it seems to be running a REORGANIZE on all Indexes for all Tables and Views and "Compact Large Objects" is checked; and then it UPDATE STATISTICS for all tables. There is also a separate job that runs after the Maintenance Plan that if the fragmentation of the same DB1 Indexes is more than 10% they are reorganized. It seems redundant. If they were just reorganized why would there need to be a second job? Also wouldn’t the second job be more efficient since it doesn't use up resources for Indexes less than 10%?


I would recommend you to remove -- shrink database, as it will cause more fragmentation. Basically you are doubling your work by performing Shrink database.

Read this : http://www.sqlskills.com/blogs/paul/why-you-should-not-shrink-your-data-files/

Also, I would suggest to use Ola's maintenance scripts which are "the best" out for SQL Server community http://ola.hallengren.com/

You should never reorganize or rebuild all your indexes as its a costly operation and should be done during maintenance window, rather depending on the level of fragmentation e.g. less than 10% ==> dont do anything 10 - 30 % ==> do a reorganize 30% an above ==> rebuild

  • Thanks for the Shrink info, and I was under the same impression about indexes greater than 10% or greater fragmentation so I made some changes. I kept the CHECKDB and SHRINKDATABASE part of the Maintenance Plan (MP1) and the disabled the Reorganizing indexes and updating statistics and instead calls the separate job to start which is now the only thing handling the indexes and statistics. The MP1 went from running for 6 hours to 20 minutes and the separate job went from running for 3 hours to 10 minutes. Seems too good to be true, did I do something wrong? This is why I asked the my question. – SQLFoenix Mar 18 '13 at 14:06
  • 1
    As I mentioned, you do not need "SHRINK" database in your maintenance plan. You can read the reference link mentioned in my answer for more clarification. If your database log is growing, then depending on your recovery model, you can take T-log backups. If your data file is growing, then more data is coming in - which is fine. Just make sure that the autogrowth settings is set to be in MB for data and log files. – Kin Shah Mar 25 '13 at 13:11

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.