Share via

Maintenance Plans

  • Article

Maintenance plans create a workflow of the tasks required to make sure that your database is optimized, regularly backed up, and free of inconsistencies. The Maintenance Plan Wizard also creates core maintenance plans, but creating plans manually gives you much more flexibility.

Benefits of Maintenance Plans

In SQL Server 2012 Database Engine, maintenance plans create an Integration Services package, which is run by a SQL Server Agent job. Maintenance plans can be run manually or automatically at scheduled intervals.

SQL Server 2012 maintenance plans provide the following features:

  • Workflow creation using a variety of typical maintenance tasks. You can also create your own custom Transact-SQL scripts.

  • Conceptual hierarchies. Each plan lets you create or edit task workflows. Tasks in each plan can be grouped into subplans, which can be scheduled to run at different times.

  • Support for multiserver plans that can be used in master server/target server environments.

  • Support for logging plan history to remote servers.

  • Support for Windows Authentication and SQL Server Authentication. When possible, use Windows Authentication.

Maintenace Plan Functionality

Maintenance plans can be created to perform the following tasks:

  • Reorganize the data on the data and index pages by rebuilding indexes with a new fill factor. Rebuilding indexes with a new fill factor makes sure that database pages contain an equally distributed amount of data and free space. It also enables faster growth in the future. For more information, see Specify Fill Factor for an Index.

  • Compress data files by removing empty database pages.

  • Update index statistics to make sure the query optimizer has current information about the distribution of data values in the tables. This enables the query optimizer to make better judgments about the best way to access data, because it has more information about the data stored in the database. Although index statistics are automatically updated by SQL Server periodically, this option can force the statistics to update immediately.

  • Perform internal consistency checks of the data and data pages within the database to make sure that a system or software problem has not damaged data.

  • Back up the database and transaction log files. Database and log backups can be retained for a specified period. This lets you create a history of backups to be used if you have to restore the database to a time earlier than the last database backup. You can also perform differential backups.

  • Run SQL Server Agent jobs. This can be used to create jobs that perform a variety of actions and the maintenance plans to run those jobs.

The results generated by the maintenance tasks can be written as a report to a text file or to the maintenance plan tables (sysmaintplan_log and sysmaintplan_logdetail) in msdb. To view the results in the log file viewer, right-click Maintenance Plans, and then click View History.

Use the following topics to get started with maintenance plans.

Description

Topic

Describes how to create a maintenance plan by using SQL Server Management Studio or Transact-SQL.

Create a Maintenance Plan

Describes how to create a maintenance plan by using the Maintenance Plan Design Surface.

Create a Maintenance Plan (Maintenance Plan Design Surface)

Documents maintenance plan functionality available in Object Explorer.

Maintenance Plans Node (Object Explorer)