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.
| Which Integrity Settings Were Used | Profiler Trace Results | Xp_Sqlmaint Syntax |
| Check database integrity, include indexes, attempt to repair any minor problems | dbcc checkdb(Pubs, REPAIR_FAST) WITH NO_INFOMSGS | -CkDBRepair (not documented) |
| Check database integrity, include indexes | Use pubs dbcc checkdb WITH NO_INFOMSGS |
|
| Check database integrity, exclude indexes | dbcc checkdb(Pubs, noindex) WITH NO_INFOMSGS | -CkDBNoIdx |
| Perform the above tests before backing up the database or transaction log | -BkUpOnlyIfClean (One of above CkDB depending on chosen settings) |
Comments on this test run:
- 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.
- 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.
- 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 - 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
- “WITH NO_INFOMSGS” is used to suppress all informational messages