Inside SQL Server Maintenance Plans

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

plan_id  database_name
36D539DF-7DE3-11D6-9855-00508BB3C376 WWW_ak
36D539E1-7DE3-11D6-9855-00508BB3C376 All Databases
36D539E2-7DE3-11D6-9855-00508BB3C376 All System Databases
36D539E3-7DE3-11D6-9855-00508BB3C376 All User Databases
  • sysdbmaintplans: Contains one row for each maintenance plan.

Sample Data From sysdbmaintplans (partial table)

Plan_id Plan_name date _created owner max _history _rows
00000000-0000-0000-0000-000000000000 All ad-hoc plans 11/13/98 3:10 REDMOND_sqlbld 0
36D539DF-7DE3-11D6-9855-00508BB3C376 ak_test_www_ak 11/14/98 3:10 NTTEST 1000
36D539E1-7DE3-11D6-9855-00508BB3C376 ak_test_all_db 11/15/98 3:10 NTTEST 1000
36D539E2-7DE3-11D6-9855-00508BB3C376 ak_test_sys_db 11/16/98 3:10 NTTEST 1000
36D539E3-7DE3-11D6-9855-00508BB3C376 ak_test_user_db  11/17/98 3:10 NTTEST 1000
  • 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:mssql7LOGwww_ak.txt” -WriteHistory -CkDB ‘

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

plan_id plan
activity duration
00000000-0000-0000-0000-000000000000 All ad-hoc plans WWW_ak test Check Data and Index Linkage 77
00000000-0000-0000-0000-000000000000 All ad-hoc plans WWW_ak test Check Data and Index Linkage 77

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:

Published with the express written permission of the author. Copyright 2002

Pages: 1 2 3


No comments yet... Be the first to leave a reply!