Like a lot of database administrators who manage many SQL Server databases, I have problems with maintenance and administration tasks. Some of these are massive jobs, which have to be created and maintained to be sure that databases are robust and reliable. One of the tools I use often to make my life a little easier is the Database Maintenance Wizard, which is used to create SQL Server maintenance plans.

While the Database Maintenance Wizard, and the plans it creates are generally good, they are not always perfect. In addition, what is going on behind the scenes when a maintenance plan is running is not so obvious. So what I recently did was to find out what was going on behind the scene using the SQL Server Profiler.

To capture the Profiler trace (using SQL Server 2000), I used the predefined Profiler template called SQLProfilerStandard, with two modifications, using:

• RPC: Starting
• SQL: BatchStarting

instead of:

• RPC: Completed
• SQL: BatchCompleted

All the other trace configuration items were left the same. Those two changes to the SQLProfilerStandard template allow us easily to capture the relevant trace data.

Profiling Integrity Maintenance Plan Options

Next, I created a Maintenance Plan using the following Integrity options (see figure as a reference point) in order to see what SQL Server statements were used.

Then, I started the Profiler trace and ran the job created by the Maintenance Plan to see what the results would be. I then went back and changed some of the settings in the above screen to see what happened as options were changed.

Below are the results, formatted into a table for easy viewing.

Comments on this test run:

1. Don’t use the option "Attempt to repair any minor problems" which causes DBCC Checkdb to be run with the option REPAIR_FAST. This option performs minor repair actions without risk of data loss. However, the given database must be in single-user mode to use this value, which presents problems if this is a production database.
   
   
2. If database is not in single-user mode, DBCC Checkdb with REPAIR_FAST, will not be run, unfortunately the text or html report files will still report a success.
   
   
3. By checking the option "Perform these tests before backing up the database or transaction log," SQL server modifies the backup jobs (full and log backup) by adding two option to the respective maintenance plans:
   
   · BkUpOnlyIfClean
   · CkDB | CkDBNoIdx | CkDBRepair
   
   
4. Don’t use the above option if you are going to add transaction log backups to the same maintenance plan, as this will cause DBCC activity before each incremental backup, hurting performance
   
   
5. "WITH NO_INFOMSGS" is used to suppress all informational messages

Profiling Optimization Maintenance Plan Options

In this step, I wanted to see happened under the covers when various Optimization options (see illustration below) from the Database Maintenance Wizard are traced with Profiler. As before, I will experiments with several different Optimization options, and run the resulting jobs manually in order to capture the traces.

Below are the results, formatted into a table for easy viewing.

Comments on this test run:

1. DBCC DBREINDEX uses free space as the inverse of the fill factor. So in the first row in the above table, free space = 0%, which is the same as a fill factor of 0. In second position we can see a value of 30% for free space, which is the same as a 70% fill factor.
   
   

2. If you choose DBREINDEX for a database, an UPDATE STATISTIC is done automatically
   
   

3. Even though the UPDATE STATISTIC command gives us the possibility to choose either a SAMPLE option of PERCENT or number of ROWS, the maintenance plan allows you only to specify PERCENT.
   
   

Profiling Other Maintenance Plan Options

I will not describe the database and log backup options of the Database Wizard because they are very straight-forward, and work like you would expect them to. However, I would like you to discuss some other maintenance plan options which are accessible only from xp_sqlmaint utility and not from the Maintenance Plan Wizard:

• HtmlRpt "e:\mssql7\LOG\ak_test.html": Creates an HTML version of the database maintenance report.
  
  

• DelHtmlRpt <time period>: Sets the retention cycle for HTML files, e.g. 3 WEEKS.
  
  

• CkCat: Runs DBCC CHECKCATALOG.
  
  

• CkAl, CkAlNoIdx: Runs DBCC NEWALLOC.
  
  

• CkTxtAl: Runs DBCC TEXTALL.

Comments on These Commands:

1. Be careful with the syntax of the above switches. They must be exactly as I listed above. For example if you type Htmlrpt (instead of HtmlRpt) the job will fail.
   
   

2. Don’t use NEWALLOC and TEXTALL. They are included for backward compatibility. CHECKALLOC replaced NEWALLOC and CHECKTABLE TEXTALL. It’s better to use CHECKDB, which performs CHECKTABLE for each table in a database to check the integrity of the data, and CHECKALLOC to check allocation of all pages.

Maintenance Plan Tables

Now for some details on how maintenance plans are stored inside SQL Server. The more you know about this, the easier it will be for you to automate the gathering of useful information about your maintenance plans, if you so desire.

There are four tables in the msdb database which are store maintenance plan information:

• sysdbmaintplan_databases: Contains one row for each database. Note that you can use special strings for group of databases like in the Database Maintenance Plan Wizard.

Sample Data From sysdbmaintplan_databases

• sysdbmaintplans: Contains one row for each maintenance plan.

Sample Data From sysdbmaintplans (partial table)

• sysdbmaintplan_history: Maintain the history information for each maintenance plan.
  
  

• sysdbmaintplan_jobs: Maintains the relationship between the maintenance plan id and the job id.

Comments on These System Tables:

Notice than there is one predefined maintenance plan named: "All ad-hoc plans" with a strange owner: "REDMOND\_sqlbld". It is used for all ad-hoc jobs using the xp_sqlmaint utility. The max_history_rows for this plan is set to 0, however there are history entries in the sysdbmaintplan_history table for it.

To demonstrate how this "special" entries works, I created a custom job with following command:

EXECUTE master.dbo.xp_sqlmaint N'-D WWW_ak -Rpt "e:\mssql7\LOG\www_ak.txt" -WriteHistory -CkDB '

After running it twice, here's what the history table looks like:

Creating a Custom Maintenance Plan

As you can see, the SQL Server Database Maintenance Plan Wizard doesn’t allow us to include all the potentially useful options (HTML report, some DBCC stuff). As workaround, I propose that you follow one these two options when creating your own maintenance plan:

1. Create each new maintenance plan using the wizard, then modify each job manually for those options not available directly from the wizard.
   
   

2. Avoid the wizard completely, and create all jobs manually using the xp_sqlmaint utility.

I recommend the following strategy for SQL Server maintenance jobs:

1. Create jobs (ad-hoc or using the wizard) for full backups of your databases with DBCC CHECKDB and CHECKCATALOG, and set up the option to perform the backup only if they didn’t report any errors. Create this job for all databases which don’t demand transaction log backups.
   
   

2. Create separate jobs for only transaction log backups for all databases which need it.
   
   

3. Create separate job(s) for data optimizations, depending on your demands.
   
   

4. For all of your jobs, add the HTML report option and direct it to a share point for easy access with a browser.

All information in this article was provided to help you simplify the management of our SQL Server environment. Please send all comments and questions to: akozlowski@amadeus.net