Inside Database Maintenance Plans

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_sqlmaintN'-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_sqlmaintN'-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.

Once you're familiar with maintenance-plan architecture and sqlmaint's utility switches, you can start manually creating maintenance tasks to gain greater control over which options execute under what conditions. You can also dynamically generate sqlmaint switches in code and execute existing plans after slightly modifying the options. For example, you could alternate backup locations every day so that you store your backups on two hard disks instead of one. You can implement maintenance tasks as scheduled jobs just like the wizard does, you can call them from T-SQL through the xp_sqlmaint extended stored procedure, or you can call the sqlmaint utility from a batch file or an application.

Creating a Maintenance Plan

The easiest way to create a maintenance plan is to run the Database Maintenance Plan Wizard in Enterprise Manager. To start the wizard, expand the Management node, right-click on Database Maintenance Plans, and select the New Maintenance Plan option. The first screen lets you select databases for the plan to operate on. You can select databases individually or use one of the predefined multidatabase options: All databases, All system databases, or All user databases. Using these options is convenient but isn't suitable for every environment because the maintenance needs of each database on a server often differ vastly. I usually create one plan that uses the All system databases option on every server and another plan that uses All user databases on development and staging servers. For production environments, I create a unique plan for each mission-critical database. However, the All databases and All user databases options provide one big benefit: Any new database you add to the server is included in the plan. So, for example, if the plan includes backups, you don't need to worry about the new databases; they'll automatically be backed up.

The next few steps in the wizard let you define maintenance-plan actions such as optimizations, integrity checks, database backups, and transaction log backups. Let's examine the options for each action. Figure 2 shows options for defining database optimizations. Selecting the first option, Reorganize data and index pages, tells SQL Server to rebuild all indexes on all tables. Depending on the existing fragmentation, regular index rebuilding can reduce the size of data and index pages. Regular defragmentation by rebuilding indexes helps speed database queries. Because defragmentation decreases the number of data and index pages in the database, SQL Server has to perform fewer reads during data retrievals. You can choose to rebuild indexes with the original index fill factor or specify a new fill factor. If, for example, you select to leave 10 percent of each page as free space, the sqlmaint utility executes DBCC DBREINDEX (TableName,", 90, sorted_data_ reorg) for each table in the database. The amount of space you can recover depends on existing fragmentation and the fill factor you use. Because index rebuilding results in table locks, reindexing a database can interfere with regular database activities, so make sure to schedule this activity when database use is lowest. Creating or rebuilding an index is a logged operation on databases that use the Full or Bulk-Logged Recovery model, so when you rebuild all of a database's indexes, you can expect the size of the next transaction log backup to be very large—sometimes close to the size of a full backup.

Because SQL Server automatically updates index statistics when it rebuilds an index, the second optimization option, Update the statistics used by the query optimizer, is available only when you don't choose to reorganize data and indexes. Selecting this option is usually unnecessary because unless you change the default database setting for the AUTO_UPDATE_STATISTICS option, SQL Server periodically updates index statistics. I recommend updating database statistics as a part of the maintenance plan only when the AUTO_UPDATE_STATISTICS option is off.

You should use the third option, Remove unused space from database files, only in special scenarios (such as when disk space is a concern) because database shrinking typically isn't recommended. When you shrink a database, SQL Server must move all used pages to the beginning of the data files and release the free space back to the OS, causing a lot of CPU and disk I/O activity. Then, as soon as your database starts growing again, SQL Server must expand the same data files. But because the files no longer occupy contiguous space in the OS, your data pages become fragmented.

Database integrity checks are the second plan action you can set up in the wizard. On the Integrity tab, which Figure 3 shows, you can specify whether you want to check just the data or both the data and the indexes. When you select the Attempt to repair any minor problems check box, the sqlmaint utility includes the REPAIR_FAST clause in the DBCC CHECKDB command. Be aware that SQL Server can make database repairs only when the database is in single-user mode. The maintenance plan tries to set the database to single-user mode before running the repair command, but if any users are in the database, the statement fails and the scheduled job reports an error and doesn't execute. Once the database is in single-user mode, no other users can connect to the database while the DBCC command with REPAIR_FAST is running, so users might not be able to use the database for a couple of minutes for each gigabyte of data in your database. Also, no other scheduled jobs can run until sqlmaint sets the database back to multiuser mode. So although BOL recommends including the repair option, you need to evaluate your database's usage patterns and schedule maintenance for times you're sure the database isn't in use or run the repair option manually if the DBCC command reports an error.

Selecting Perform these tests before backing up the database or transaction log results in execution of the DBCC CHECKDB command before every full backup or log backup. If the DBCC check finds a problem, sqlmaint won't proceed with the backup. This behavior is designed to prevent backups of a possibly corrupted database, but something as simple as a corrupt index or sqlmaint being unable to get into single-user mode will leave backups undone and put data at risk. If you choose this option, make sure you monitor the status of the integrity-check job so that you can fix the database immediately if necessary and resume performing backups.

The other consideration for this option is the frequency of integrity checks. Integrity checks are resource-intensive; they result in table locks and therefore can affect database and server performance and interfere with the applications accessing the database. You should perform them only when the database usage is at a minimum. Unfortunately, the wizard doesn't let you choose to perform integrity checks before full backups but not before log backups. This limitation makes this option unsuitable for production databases that use frequent log backups. I've seen a large production database with this option turned on performing integrity checks and log backups every hour. The DBCC command took about 20 minutes to finish, so the database server was running resource-intensive integrity checks about one-third of the time.

To work around this problem, DBAs often create two maintenance plans for each database, one to perform full backups with integrity checks and another to perform just log backups. While I was researching this article, I discovered another workaround. If you select the wizard option to perform integrity checks before backups, the scheduled jobs for full backups and log backups include the following two switches in the call to xp_sqlmaint:

-BkUpOnlyIfClean -CkDB

If you manually remove these two switches from the definition of the wizard-created transaction log backup job, the integrity checks won't execute before log backups, but they'll still execute before full backups. To find the transaction log backup job, expand the Management node in Enterprise Manager and go to Jobs under the SQL Server Agent node, as Figure 4 shows. If you haven't manually changed the job name since the wizard created it, the name is Transaction Log Backup Job for DB Maintenance Plan 'PlanName'.

You can use the same technique to fix the problem resulting from a bug that the Microsoft article "BUG: DB Maintenance Plan Cannot Be Modified to Include/Exclude Integrity Checks Before Backups" (http://support.microsoft.com/?kbid=264194) describes. This bug prevents you from modifying an existing plan to include or exclude integrity checks before backups, so you're stuck with the option you select when you create the plan. Given the limitation of the options screen and the behavior resulting from the bug, your best option is probably to maintain these two switches manually in the scheduled job commands.

Database Backup Options

After you configure the integrity checks, the wizard lets you set up the most important database-maintenance plan actions: complete backup and transaction log backup. The backup options, which Figure 5 shows, are identical for full and log backup actions. Unfortunately, maintenance plans don't support differential backups, even though they've been around since SQL Server 7.0. Maintenance plans don't support file backups or filegroup backups, either, so the full backup part of the maintenance plan isn't always suitable for large databases that would benefit from using file or filegroup backups. You can configure your plan to verify whether the backup set is complete and readable by selecting the Verify the integrity of the backup upon completion check box. If you have a tape drive, you can specify it as the backup location, but for performance reasons, experts generally recommend that you direct SQL Server backups to a local hard disk and back up the files from the hard disk to a tape. One of my favorite features of the sqlmaint utility is the option to automatically remove old files; you can set up retention periods ranging from minutes to months.

I ran into a few problems with transaction log backups in maintenance plans. If you include a log backup for a database that uses the Simple Recovery model, the wizard doesn't warn you that this operation is illegal, and the scheduled job correctly reports failure—but only in SQL Server 2000. SQL Server 7.0 has a bug that the Microsoft article "BUG: Sqlmaint Does Not Report Error on BACKUP LOG When Truncate Log on Checkpoint is Set" (http://support.microsoft.com/?kbid=242500) describes. It doesn't report this error as a failure. You might need to fix this problem when you upgrade to SQL Server 2000 if jobs that used to run with no errors start generating errors. This bug is somewhat related to a SQL Server 2000 bug documented in the Microsoft article "BUG: Expired Transaction Log Backups May Not Be Deleted by Maintenance Plan" (http://support.microsoft.com/?kbid=303292). According to this article, if a maintenance plan performs log backups on multiple databases and at least one of the databases uses the Simple Recovery model, the sqlmaint utility doesn't remove expired backups for all databases. If you run into this problem, you must either configure all databases in the plan to use the Full or Bulk-Logged Recovery model or create a second maintenance plan for all databases that use the Simple Recovery model. You'd need to configure the plan to not perform transaction log backups.

Monitoring and Notifications

The Reporting tab, which Figure 6 shows, lets you define several reporting options for the maintenance plan. You can output status reports to text files and have SQL Server automatically delete them when they expire. The sqlmaint utility can alternatively output reports to HTML files, but the Reporting tab doesn't let you set up this option. Instead, you need to modify the calls to xp_sqlmaint in the scheduled jobs to include the -HtmlRpt and -DelHtmlRpt switches. You can also choose to log plan history to the sysdbmaintplan_history table either on the local or a remote server.

The easiest way to examine the history of maintenance-plan executions is to use the Database Maintenance Plan History dialog box, which Figure 7 shows. You open this dialog box by right-clicking the Database Maintenance Plans node in Enterprise Manager and selecting Maintenance Plans History. Because this form provides comprehensive filters, drilling down through the thousands of rows in the sysdbmaintplan_history table is easy. You can also write views to provide custom reports. If you manage multiple servers, you can set up all servers to write to the sysdbmaintplan_history table on just one server. Another option is to create a view that uses the UNION clause to read the sysdbmaintplan_history table on multiple linked servers. The history table and sqlmaint text reports provide more details about task failures than you can gather from the scheduled job history. When I notice in Enterprise Manager that a maintenance-plan job failed, I prefer to view the maintenance plan history because the scheduled job history often reports only "sqlmaint.exe failed" in the error description.

In mission-critical environments, you can't rely solely on someone regularly reviewing the history table or reading the plan reports to find out something went wrong. If a backup failure occurs, you want to be notified immediately so that you can take corrective action. Neither the wizard nor the maintenance-plan screen lets you set up notifications for maintenance-plan failures. To define notifications, you need to edit the scheduled jobs the wizard generated. SQL Server modifies the job schedule and the job command when you edit the maintenance plan, but it leaves the job's Notifications section intact. A scheduled job gives you three notification options: Email operator, Page operator, and Net send operator. If you later drop and recreate a maintenance plan, you need to set up these notifications again.

SQL Server database maintenance plans give you a powerful and flexible mechanism for defining and providing for your databases' maintenance needs. By creating optimal plans, you can vastly reduce the time you need to spend maintaining and monitoring your databases.