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%?
Tell me more
×
Database Administrators Stack Exchange is a question and answer site for
database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
|
|
|
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 |
|||||
|
