Overview of Maintenance Plans in SQL Server 2008

The Maintenance of a SQL Server databases is one of the primary responsibilities of a Database Administrator. To make database maintenance easier, DBA's can leverage the Maintenance Plan Wizard or Maintenance Plans which are available in SQL Server 2005 and higher versions. A DBA can create a database maintenance plan either by using the Maintenance Plan Wizard or by using the SQL Server Integration Services (SSIS) designer. By using the Maintenance Plan Wizard you can create a very basic maintenance plan for all the system and user databases. However, if you want to create an enhanced workflow then it is advised to create a maintenance plan using the SSIS designer. After reading this article you will understand in detail the different types of maintenance plan tasks that are available and their usage. We will also show how to create a basic SQL Server Maintenance Plan with some of the tasks available using the Maintenance Plan Wizard option.

Permissions Required to Create & Manage Maintenance Plans
You need to be a member of the sysadmin fixed server role to create and manage maintenance plans. Maintenance plans are only visible to those users who are member of the sysadmin fixed server role.

Different Types of Maintenance Plan Tasks
Let's look at the different types of Maintenance Plan tasks in SQL Server 2005 and SQL Server 2008 and their usage:

Back Up Database Task
The Back Up Database task allows you to perform different types of database backups like Full, Differential or Transactional Log backups based on the Recovery Model of the System or User databases. To know more about the different recovery models in SQL Server you can refer to my previous article titled “Database Recovery Models in SQL Server”. In the Maintenance Plan Wizard this task is subdivided into three individual tasks namely Back Up Database (Full), Back Up Database (Differential) & Back Up Database (Transaction Log). If you have chosen only a single database then it will even allow you to perform file and filegroup backups. Once the database backup has completed successfully within this task there is an option to verify the database backup integrity. In SQL Server 2008 Microsoft has introduced the Database Backup Compression feature and it is now possible to use the compressed database backups feature within the “Back up Database Task”. To know more about the database backup compression feature of SQL Server 2008 you can refer to my previous article titled “How to configure and Use Database Backup Compression in SQL Server 2008”

Check Database Integrity Task
The Check Database Integrity Task can be used to check the allocation and structural integrity of all the user and system tables within a database. This task also has an option to check the allocation and structural integrity of all the indexes available within a database. Using this task you can make a choice of database against which you want to perform the database integrity checks. This task internally executes the DBCC CHECKDB statement.

Execute SQL Server Agent Job Task
The Execute SQL Server Agent Job Task can be used to run a SQL Server Agent Job which is created on the SQL Server Instance. This task is only available when you are creating a Maintenance Plans using SSIS designer.

Execute T-SQL Statement Task
The Execute T-SQL Statement Task can be used to execute Transact SQL queries against a database. This task is only available when you are creating a Maintenance Plans using SSIS designer.

History Cleanup Task
The History Cleanup Task basically deletes the historical data related to database backups and restore activities, SQL Server Agent Job history, database maintenance plan history etc. This task basically uses the sp_delete_backuphistory system stored procedure to clean up the history prior to the number of days, weeks or months from the current system date.

Maintenance Cleanup Task
The Maintenance Cleanup Task can be used to remove the older files like maintenance plan execution reports, database backup files etc. You should use this task when you are creating maintenance plans as this will remove old files which are not required. 

Notify Operator Task
The Notify Operator Task can be used to send messages to the SQL Server Agent Operator when a task has successfully completed or failed. The operator can be notified by an email, pager or by net send method.

Reorganize Index Task
The Reorganize Index Task is used to defragments & compact both clustered and non-clustered indexes on tables and views. The index reorganize is best suited when the index are not heavily fragmented. This process takes very less system resources when compared to rebuilding an index. If the indexes are heavily fragmented then the best choice will be to rebuild indexes using the Rebuild Index Task.

Rebuild Index Task
The Rebuild Index task can be used to organize data which is there on the data and index pages by rebuilding indexes. This helps to improve the performance of index seeks and index scans. This task also optimizes the distribution of data and free space on the index pages, thereby allowing faster future growth. If this task is used to rebuild the indexes in a single database then it allows you to choose views and tables for which you want to rebuild the index. This task also has options such as “Sort results in tempdb” and “Keep index online while reindexing”. However these operations require sufficient disk space in tempdb database. 

Shrink Database Task
The Shrink Database Task can be used to reduce the disk space which is consumed by the database and log files by removing the empty data and log pages. Using this task the space gained after shrinking the database can either be returned to the operating system or it can be retained within the database for its future growth. This task basically executes DBCC SHRINKDATABASE statement.

Update Statistics Task
The Update Statistics task ensures that the query optimizer has up-to-date information about the distribution of data values within the tables. This allows the optimizer to make better judgments about the data access strategies it needs to choose. The Update Statistics task basically executes UPDATE STATISTICS statement.