Maintenance plans enable you to set up a predefined
set of routine maintenance tasks on your SQL Server instances. You can
create a maintenance plan by using the Maintenance Plan Wizard or going
straight to the Maintenance Plan Design tab. Just like all wizards in
SQL Server, the Maintenance Plan Wizard is a pretty straightforward
point-and-click operation. The Maintenance Plan Design tab however,
gives you a few extra options and provides a little more control over
the maintenance tasks. Maintenance plans are nothing more than SQL
Server Agent jobs that execute SQL Server Integration Services packages.
1. Maintenance Plan Wizard
The Maintenance Plan
Wizard provides a simple interface for you to define and schedule tasks
that perform routine maintenance. Right-click the Maintenance Plans
folder located under the Management node and select Maintenance Plan
Wizard from the context menu. Click Next when the Welcome screen appears
to display the Select Plan Properties page, as shown in Figure 1.
Name the maintenance plan
and provide a brief description. The main thing to point out on this
page is the scheduling options. If you select Separate Schedules for
Each Task, a separate SQL Server Agent job will be created for each
task, and each will be located in its own subplan. The scheduling option
is disabled on this page, and you will need to provide a separate
schedule when configuring each individual task. If you select Single
Schedule for the Entire Plan or No Schedule, each task will be included
in a single subplan using a single SQL Server Agent job, and the
individual scheduling options for each task will be disabled. Click Next
to display the Select Maintenance Tasks page, shown in Figure 2.
You can view a brief
description of each task by highlighting it. For example, you can see a
description of the Update Statistics task that is highlighted in Figure 13-23. Select the tasks you want to include in the maintenance plan and continue through the wizard to configure each task.
2. Maintenance Plan Design Tab
You can use the
Maintenance Plan Design tab to build a maintenance plan from scratch or
use it to tweak plans created by the Maintenance Plan Wizard. One of the
things that bothers us about the Maintenance Plan Wizard is the
inability to rename the subplans during the setup process. As a result,
you end up with SQL Agent jobs named MaintencePlanName.SubPlan_1,
MaintencePlanName.SubPlan_2, and so on, for each task that requires a
separate job schedule. This naming convention tells you nothing about a
given job. For example, does it rebuild indexes, update statistics, back
up a database, or what? So the first thing you should do after using
the Maintenance Plan Wizard is open the Design tab and rename the
subplans to something more descriptive.
To open a plan that has been
created using the wizard, just double-click Maintenance Plan located in
the Maintenance Plans folder under the Management node in the SQL Server
Management Studio Object Explorer. To create a new maintenance plan
using the Design tab, right-click the Maintenance Plans folder located
under the Management node and select New Maintenance Plan. Enter a name
for the new plan when prompted, and click OK. (The Maintenance Plan
Design tab is shown in Figure 3.)
If you have ever
created a SQL Server Integration Services package, the Design tab should
look extremely familiar. From here you can add, remove, and configure
subplans (including giving them a descriptive name). Once you have
configured the subplans to perform the appropriate maintenance tasks,
save the maintenance plan to create or update the SQL Server Agent jobs
that run them.
3. Maintenance Plan Tasks
Now that we have discussed how
to create and edit maintenance plans, let's review the available
maintenance tasks. There are two maintenance tasks available in the
Design tab that are not available in the wizard: Execute T-SQL Statement
Task and Notify Operator Task. You may find these two extra tasks
useful if you want to perform more customized operations in your
maintenance plans. The following is a list of tasks you can perform with
maintenance plans.
Backup Database Task: You can use this task to perform a full, differential, or transaction log backup for one or more databases.
Check Database Integrity Task: You can use this task to execute the DBCC CHECKDB command against one or more databases.
Execute SQL Server Agent Job Task: You can use this task to select an existing SQL Server Agent job to run as part of the maintenance plan.
Execute T-SQL Statement Task: You can use this task to execute a custom T-SQL statement as part of the maintenance plan.
History Cleanup Task:
You can use this task to clean up backup and restore history, SQL
Server Agent job history, and maintenance plan history from the msdb that is older than a specified date.
Maintenance Cleanup Task:
You can use this task to delete files such as database backups and
maintenance plan text files that are older than a specified date.
Notify Operator Task: You can use this task to send a notification to an existing operator as part of the maintenance plan.
Rebuild Index Task: You can use this task to rebuild all indexes in one or more databases, or you can target specific objects for index rebuilds.
Reorganize Index Task:
You can use this task to reorganize all indexes in one or more
databases or you can target specific objects for index reorganization.
Shrink Database Task:
You can use this task to shrink one or more databases, though you
really should not have to shrink databases as a part of your maintenance
plan. Constantly shrinking and growing your databases will greatly
reduce performance.
Update Statistics Task: You can use this task to update statistics in one or more databases.
You can reproduce every
maintenance task using T-SQL. There is no magic going on in the
background. We are not against maintenance plans, but we tend to create
our own maintenance jobs using T-SQL scripts. We like a little more
control than the maintenance plan will allow. For example, instead of
blindly rebuilding an index, we check the fragmentation level first. If
the fragmentation level is between 5% and 30%, we reorganize the index.
If the fragmentation level is greater than 30%, we rebuild the index.
For more information on finding index fragmentation levels, search for
"Reorganizing and Rebuilding Indexes" in SQL Server Books Online.
4. Exporting and Importing Maintenance Plans
Connect to Integration Services
on the SQL Server instance that contains the maintenance plan you would
like to export, expand the Maintenance Plans folder, right-click the
maintenance plan and select Export Package, as shown in Figure 4.
This will open the Export Package dialog box shown in Figure 5.
You can use this dialog box to save the maintenance plan to another
instance of SQL Server, or save it to the file system so you can import
the maintenance plan on the new SQL Server instance later.
If you are importing the
maintenance plan from the file system, you will need to connect to
Integration Services on the new SQL Server instance and right-click the
Maintenance Plans folder and select Import Package to open the Import
Package dialog box shown in Figure 6.
Once you have imported
the maintenance plan on the new instance of SQL Server, you will need to
open it using the Design tab and click the Save button in order to
create the jobs associated with the plan.