Database Maintenance Plans

Create a Database Maintenance Plan to maintain databases. Database integrity options include Check database integrity and Include indexes, which test the data and index page allocations in the databases for any errors. These tests are resource intensive and impair the performance of the server during the tests. These tests should only be run during off hours.

The database maintenance plan screen also has an option called Perform these tests before backing up the database or transaction log. This is not a good choice to make from a performance standpoint. If chosen, every time the Maintenance Plan is used to perform a database or transaction log backup--without exception--the integrity tests are automatically run. If the databases are large, and/or if frequent database or transaction log backups occur, running these tests this often can significantly degrade the server's performance. The recommended configuration consists of creating separate scheduled SQL tasks (in SQL Agent) to perform database/log backups and database integrity checks.

It is imperative to run backups, DBCC CHECKDB, and index rebuilds or reorgs regularly! It is equally imperative that indexes rebuilds and reorgs are done selectively. All indexes should not be rebuilt or reorged during the same job!