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%?
- Anybody can ask a question
- Anybody can answer
- The best answers are voted up and rise to the top
I would recommend you to remove -- shrink database, as it will cause more fragmentation. Basically you are doubling your work by performing Shrink database.
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