605
Chapter 22: Maintaining the Database
22
Planning Database Maintenance
Database maintenance plans include the following routine tasks:■ Check database integrity.■ (^) Perform index maintenance.
■ Update database statistics.
■ (^) Perform database backups.
These maintenance tasks can be automated and scheduled using SQL Server Agent service.
Once created, the maintenance plans (and subplans) are scheduled in SQL Server Agent as
jobs.
Maintenance Plan
Following are two ways to create a Maintenance Plan:
■ Maintenance Plan Wizard: Used to quickly create a basic maintenance plan. This
enables you to choose one of the predefi ned typical maintenance tasks, such as
performing database backup, rebuilding indexes, updating statistics, checking data
integrity and cleaning up history and backup fi les.. It does not enable you to add
any custom tasks.
■ (^) Maintenance Plan Design Surface: Used to design maintenance plans with more
fl exibility. This enables you to create a workfl ow of typical maintenance tasks and
create custom maintenance tasks using T-SQL scripts. It also enables extended log-
ging, which can prove to be useful for troubleshooting purposes.
All the maintenance plans appear in the Management\Maintenance Plans folder of SQL
Server Management Studio. To launch the Maintenance Plan Wizard, right-click the
Maintenance Plans folder, and choose Maintenance Plan Wizard. Follow the simple step-by-
step wizard to create the maintenance plan.
To launch the Maintenance Plan Design Surface, right-click the Maintenance Plans folder,
and choose New Maintenance Plan from the context menu. Type a name for the mainte-
nance plan in the New Maintenance Plan dialog box, and click OK.
After you assign a name to your maintenance plan, Management Studio opens a new cen-
ter window that includes the maintenance plan name, a description, the time you want to
schedule the maintenance plan, and a list of tasks to perform. Figure 22-1 shows a sample
maintenance plan with some tasks already entered.
c22.indd 605c22.indd 605 7/31/2012 9:24:32 AM7/31/2012 9:24:32 AM
http://www.it-ebooks.info