Database Maintenance Plans Guidelines
There should be two separate maintenance plans created, the first for all system databases and the second for all user databases. As well as performing database backups these maintenance plans should also be configured to perform optimisations as well as integrity checks.
Optimisations
Both maintenance plans should be configured to reorganise data and index pages and change free space per page to a given percentage (usually 10%). This process causes the indexes on the tables to be dropped and recreated with the specified amount of free space reserved for growth (this reduces fragmentation).
Both maintenance plans should be configured to remove unused space from database files and shrink the database once it grows beyond a reasonable figure (always keep at least 10% of free space after the shrink).
The above tasks should be run at least once per week.
Integrity Checking
Both maintenance plans should be configured to check database integrity and include indexes. However it is not advisable to attempt to repair any minor problems. These checks should also be configured to run before any backup is performed. This task should be scheduled to run at least once per week.
Backups
Complete and transaction log backups should be configured to write to a specified directory on local disk. A sub-directory should be created for each database as well as allowing removal of backup files older than a given date (this is dependent on the size of the backups and the available disk space).
In order to perform transaction log backups the databases have to be in full or bulk-logged recovery modes. It is not possible to perform a transaction log backup of the master database thus they should not be configured within the maintenance plan for system databases.
Reporting
All database maintenance plans should be configured to write reports to a given directory, delete reports older than a given period and send email notifications to the relevant operator.
The database maintenance plans should also be configured to allow history to be written to the table MSDB.DBO.SYSDBMAINTOPLAN_HISTORY on the local server. This allows the job history to be displayed by executing the stored procedure SP_HELP_JOBHISTORY against the MSDB database.
|