Regular maintenance is essential for the smooth, successful operation of your databases. This maintenance includes making backups to protect against data loss, performing consistency and integrity checks to ensure your data and indexes aren't corrupted, and regularly rebuilding indexes to help you compact and defragment your data. SQL Server database maintenance plans help define, automate, and monitor these maintenance operations. The Database Maintenance Plan Wizard, which walks you through the creation of a maintenance plan, usually selects suitable defaults for each maintenance area. However, to ensure that the wizard's instructions match your intention, you need to understand how each option works, which T-SQL commands SQL Server will execute when the plan is running, how these commands can affect database and server performance, and what effect the plan will have on disk-space requirements.
Database Maintenance Plans Architecture
To create database maintenance plans, you can use the Database Maintenance Plan Wizard. The wizard walks you through the process of selecting one or more databases, then lets you select maintenance actions to include in the plan. Several system tables in the msdb database store most of the details about the plans and their maintenance actions. When you create a database maintenance plan, SQL Server creates an entry in the sysdbmaintplan table. Each time you execute an action (e.g., an integrity check, an optimization, a backup) that you defined in the plan, SQL Server makes an entry in the sysdbmaintplan_history table. The first step in the wizard lets you define which databases the plan should operate on. The sysdbmaintplan_databases table stores the databases you select. This table contains one row for each database associated with the maintenance plan. If you select a multidatabase option, the table stores only one row, which contains one of three entries in the database_name column: All Databases, All User Databases, or All System Databases.
The next few steps in the wizard let you define one of four maintenance actions: optimizations, integrity checks, database backup, or transaction log backup. The wizard creates a SQL Server Agent job for each action you include in the plan and stores these jobs in the sysjobs table. The wizard stores the link between the maintenance plan and each job in the sysdbmaintplan_jobs table. Each job contains one step, which is a call to the xp_sqlmaint extended stored procedure along with a parameter containing utility switches. You can think of utility switches as the equivalent of command-line parameters for executable files that have no user interface. SQL Server executes database maintenance plans as background processes with no user interaction, so these utility switches function as parameters that define what the plan should do when executed. The wizard generates these switches for the options you selected for each plan action. The job command in Figure 1 contains a sample call to xp_sqlmaint, which invokes the sqlmaint utility and passes along the utility switches. Sqlmaint is the core of SQL Server database maintenance plans: It processes the utility switches, generates T-SQL commands, and sends them to SQL Server. It can also delete old backup files and generate text and HTML report files. SQL Server Books Online (BOL) contains the complete list of sqlmaint utility switches.
While examining the maintenance-plan system tables, I noticed something interesting. None of the system tables stores which actions you included in the plan or which options you selected for each action. The system tables contain only links between a plan and each of its scheduled jobs. When you open a maintenance plan for editing, Enterprise Manager reads each associated job and parses the call to xp_sqlmaint to extract the options you selected at plan creation. For example, if your plan includes the Optimizations action and you selected the Change free space per page percentage to: 10% option, as Figure 2 shows, the call to xp_sqlmaint includes "-RebldIdx 10" as one of the action definitions. "RebldIdx 10" is translated at runtime to T-SQL commands that rebuild all indexes in the database and leave 10 percent of each index page free. Note that SQL Server doesn't store this definition in the maintenance-plan system tables. This implementation makes a lot of sense. If these definitions were stored in the system tables, you might find that the definitions in the system tables no longer match the definitions in the scheduled jobs because nothing prevents someone from manually modifying a maintenance plan's scheduled jobs.
When you run the sqlmaint utility and specify the plan name or plan ID, the only thing that SQL Server retrieves from the system tables is the list of databases you included in the plan. This means that when you manually execute a maintenance plan, sqlmaint executes the actions you specify as parameters at runtime, not the actions you specified when you used the wizard to create the plan. For example, let's assume you have a wizard-created plan that includes all databases, performs a full backup to the C:\BACKUP folder, and deletes backup files older than 1 week. The wizard generates a job whose definition looks like this:
EXECUTE master.dbo.xp_sqlmaint
N'-PlanID 54442E44-EF8D-488A-
AF39-FFBB3CE62D1D -BkUpMedia
DISK -BkUpDB "C:\BACKUP"
-DelBkUps 1WEEKS '
Copy the above code to Query Analyzer and replace the backup switches with the -CkDB switch, which tells SQL Server to perform only the DBCC CHECKDB command for each database in the plan:
EXECUTE master.dbo.xp_sqlmaint
N'-PlanID 54442E44-EF8D-488A-
AF39-FFBB3CE62D1D -CkDB '
When you run the modified code, sqlmaint executes the maintenance plan, but the plan doesn't perform full backups even though that's how you defined it. It performs only integrity checks because those are the only switches you passed in at runtime.
Prev. page
[1]
2
3
next page 
