About Creating Maintenance Plans
Use the Design pane to create a workflow of database maintenance tasks. Tasks can execute independent of another task's status, or can be dependent on another task's completion before they can begin execution.
You can add additional subplans in a maintenance plan to group related tasks or to schedule tasks to execute at different times.
NOTE: This topic focuses on information that may be unfamiliar to you. It does not include all step and field descriptions.
To create a maintenance plan
- Select Maintenance Plans in the Navigation pane (CTRL+4).
- Right-click a server instance and select Create New Maintenance Plan in the Maintenance Plan pane.
- Select Maintenance Plans in the Object Explorer, and click
.
-
Drag tasks to the Design pane to add them to the subplan. Double-click a task to specify its properties.
Tip: If you need to create several similar plans, you can simply copy existing plans or subplans and then make the necessary edits. Copy Maintenance Plans
Tip: For legacy maintenance plans only:
- You cannot have more than one task of the same type in a subplan. To add a duplicate task, create a new subplan and add the task.
- You cannot have both a Fast Compression Backup task and Back Up Database task added to the same subplan.
- Any tasks in the subplan must use the same list of databases. If you select a different database in a task, LiteSpeed prompts you to apply the database change to the entire subplan.
Task Add this task to... Available for legacy plans Fast Compression Backup
Perform full or differential backups using the Fast Compression technology.
NOTE: Fast Compression is only available with LiteSpeed 5.1 or later; Enterprise license.
With LiteSpeed only Back Up Database
Perform full, differential, or transaction log backups; with or without encryption. You can back up databases on multiple servers by adding a separate backup task to the maintenance plan for each server.
Yes
Smart Clean Up Backup Files
Remove backup files with SmartCleanup
With LiteSpeed only
Check Database Integrity
Validate the following:
- Disk space allocation
- Page and structural integrity for tables and indexed views
- Catalog consistency
- Contents of indexed views
- Service Broker data
Yes
Reorganize Index
Defragment and compact existing indexes to improve performance. With LiteSpeed only Execute Job
Execute an existing SQL Server Agent job. No
Clean Up Maintenance Plans
Remove obsolete backup files and reports created by a maintenance plan.
With LiteSpeed only
Clean Up History
Remove historical data from the msdb database and LiteSpeed Local and Central repositories for the following:
-
Back up and restore history
-
SQL Server Agent jobs
-
Maintenance plans
-
LiteSpeed activity
NOTE: LiteSpeed activity is removed from the Central repository, only if it is located on the same server, where the Local repository resides.
-
Any information for deleted databases
-
Log shipping history
-
Status history (Job, DTS, Maint Plans)
With LiteSpeed only
Notify Operator
Send an email notification to one or more existing operators. You can use the notification profiles only if they are already configured within a SQL Server instance.
TIP: Variables defined in the maintenance plan can be used to format the Subject Line. There is also help information inside the task that lists the available Subject Line variables.
NOTE: The SQL Server Agent must be configured to send email using Database Mail. Review the following for additional information:
With LiteSpeed only
Rebuild Index
Drop and recreate an index to improve performance.
Yes
Shrink Database
Reduce the size of data and log files in a database that grow beyond a specified size.
Yes
Execute T-SQL
Execute statements or batches on one or more databases.
NOTE: SSIS maintenance plans support only the Transact-SQL command type.
Yes
Update Statistics
Update column and index statistics.
Yes
-
(SSIS Plans only) Right-click a task to execute first and select Add Constraint to draw a line to the dependent task.
Tips:
- You can add multiple constraints for a single task, and can execute those tasks concurrently or based on the outcome of the previous task.
- To use edit the constraint or use an expression to evaluate precedence, right-click the constraint line and select Edit.
NOTE: Constraints are not available for legacy plans. Legacy plan tasks are executed in the following sequence:
1. Clean Up History
2. Check Database Integrity
3. Rebuild Index
4. Shrink Database
5. Update Statistics
6. Reorganize Index
7. Fast Compression Backup or Back Up Database
8. Clean Up Maintenance Plans
9. Notify Operator
10. Execute T-SQL - Click
to enter or select a schedule for executing the current subplan.
- Click
and repeat steps 2, 3 and 4 for additional subplans you want to add.
-
(Optional) Set reporting options.
Reporting and Logging Click
to set the reporting and logging options. Reporting and Logging in Maintenance Plans
Notifications Click to notify an operator of job (subplan) status when a job fails, succeeds, or completes.
Tips:
- To manually execute, edit, or delete a maintenance plan, right-click the plan in the
- To change the plan owner, open the plan in the Designpane and select the owner from the drop-down list in the upper-right corner of the pane. Note that the plan owner is only responsible for creating and editing plans. The account that executes packages is the SQL Agent service account (or a proxy account). Privilege and Grant Requirements
- To remove a subplan, select it and click
- To disable a subplan, double-click the subplan in the Design pane.
- To define any connections you want to use for tasks in a maintenance plan, click
. Once you add a connection, you can select it from the Connection drop-down list in any task. You can also define a connection at the task level that applies to other tasks and subplans. For legacy plans, you can add only one connection for remote logging and you must use Windows Authentication.
