| title | Maintenance plans | |||
|---|---|---|---|---|
| description | Learn about maintenance plans, which create a workflow to ensure that your SQL Server database is optimized, regularly backed up, and free of inconsistencies. | |||
| author | MikeRayMSFT | |||
| ms.author | mikeray | |||
| ms.reviewer | randolphwest | |||
| ms.date | 03/27/2023 | |||
| ms.service | sql | |||
| ms.subservice | supportability | |||
| ms.topic | concept-article | |||
| f1_keywords |
|
|||
| helpviewer_keywords |
|
[!INCLUDE SQL Server]
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.
In the [!INCLUDE ssde-md], maintenance plans create an [!INCLUDEssISnoversion] package, which is run by a [!INCLUDEssNoVersion] Agent job. Maintenance plans can be run manually or automatically at scheduled intervals.
[!INCLUDEssnoversion] maintenance plans provide the following features:
-
Workflow creation using various typical maintenance tasks. You can also create your own custom [!INCLUDEtsql] 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 [!INCLUDEssNoVersion] Authentication. [!INCLUDEssNoteWinAuthentication]
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 [!INCLUDEssNoVersion] 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 hasn't 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 [!INCLUDEssNoVersion] Agent jobs. This can be used to create jobs that perform various 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 select View History.