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 _name	database _name	server _name	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: akozlowski@amadeus.net

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

]]>