Supporting tables for SQL Server 2005 style maintenance plans

By: Greg Robidoux   |   Comments   |   Related: > Maintenance

Problem

As you have probably found out there are a lot of things that have changed with SQL Server 2005 and quite a few things that have remained the same.  One of the changes that has occurred is how to setup maintenance plans in SQL Server 2000 vs. SQL Server 2005.  This differences in setting up a maintenance plan were covered in this previous tip. Another change that has taken place is where the data about maintenance plans is stored. The old tables for maintenance plans still exists, but where is the data for the new plans?

Solution

Since SQL Server 2005 allows you to bring over some legacy components such as Maintenance Plans, the tables that support the old style still exist.  These tables are still in the MSDB database and are as follows:

• sysdbmaintplans
• sysdbmaintplan_jobs
• sysdbmaintplan_history
• sysdbmaintplan_databases

Here are a few simple queries to gather information about SQL Server 2000 style maintenance plans.

SELECT plan_name  
FROM msdb.dbo.sysdbmaintplans

SELECT plan_name, database_name, activity, start_time, duration  
FROM msdb.dbo.sysdbmaintplan_history 
WHERE start_time > '11/01/2006'

SELECT plan_name, database_name  
FROM msdb.dbo.sysdbmaintplans 
INNER JOIN msdb.dbo.sysdbmaintplan_databases 
ON msdb.dbo.sysdbmaintplans.plan_id = msdb.dbo.sysdbmaintplan_databases.plan_id

For SQL Server 2005 the process is now done using SSIS to create maintenance plans. Because of this change, as well as the need to support the old and new style, new tables have been created to store the data.  There is actually a mix of views and tables for the new approach as the following list shows:

• sysmaintplan_plans (view over table msdb.dbo.sysdtspackages90)
• sysmaintplan_subplans
• sysmaintplan_log
• sysmaintplan_logdetails

Here are a few simple queries to gather information about SQL Sever 2005 style maintenance plans.  As you can see from these sample queries uniqueidentifier values are still used for maintenance plans.

Query 1 - display a list of maintenance plans

SELECT s.name  
FROM msdb.dbo.sysmaintplan_plans

Query 2 - display a list of plan and subplan IDs for maintenance plans

(use the name from above query results for this step)

SELECT 
ID, sp.subplan_id 
FROM msdb.dbo.sysmaintplan_plans AS s 
INNER JOIN msdb.dbo.sysmaintplan_subplans AS sp ON sp.plan_id=s.id 
WHERE s.name=N'MaintenancePlan'

Query 3 - Display history information

(use the planID and subPlandID from above query results for this step)

SELECT spl.task_detail_id AS [ID],  
spl.start_time AS [StartTime],  
spl.end_time AS [EndTime],  
spl.succeeded AS [Succeeded]  
FROM msdb.dbo.sysmaintplan_plans AS s 
INNER JOIN msdb.dbo.sysmaintplan_subplans AS sp ON sp.plan_id=s.id  
INNER JOIN msdb.dbo.sysmaintplan_log AS spl ON spl.subplan_id=sp.subplan_id  
WHERE sp.subplan_id=N'f9f27b1c-b26e-448e-8531-899ccfa817ed' 
AND s.id=N'a8a6cbd6-6d58-453b-a4e5-d6dfe187e7a5'  
ORDER BY [EndTime] DESC

Query 4 - Display detail information

(use the planID and subPlandID from the query 2 results for this step)

SELECT  
ld.line1 AS [Line1],  
ld.line2 AS [Line2],  
ld.line3 AS [Line3],  
ld.line4 AS [Line4],  
ld.line5 AS [Line5],  
ld.server_name AS [ServerName],  
ld.start_time AS [StartTime],  
ld.end_time AS [EndTime],  
ld.error_number AS [ErrorNo],  
ld.error_message AS [ErrorMessage],  
ld.command AS [Command],  
ld.succeeded AS [Succeeded]  
FROM msdb.dbo.sysmaintplan_plans AS s  
INNER JOIN msdb.dbo.sysmaintplan_subplans AS sp ON sp.plan_id=s.id  
INNER JOIN msdb.dbo.sysmaintplan_log AS spl ON spl.subplan_id=sp.subplan_id  
INNER JOIN msdb.dbo.sysmaintplan_logdetail AS ld ON ld.task_detail_id=spl.task_detail_id
WHERE (sp.subplan_id=N'f9f27b1c-b26e-448e-8531-899ccfa817ed') 
AND(s.id=N'a8a6cbd6-6d58-453b-a4e5-d6dfe187e7a5') 
ORDER BY [StartTime] DESC

So the data for this new approach does exist it is just stored in a different place. If you are using the old tables in your processes you will need to adjust your processes to take advantage of these new tables and the new way of handling maintenance tasks in SQL Server 2005.

Next Steps

• Most of this information is readily available from the Management Studio, but depending on how large your environment is it is often useful to write your own reports and procedures to capture this information
• Take these code snippets and write a query or a view that encompasses all of the data instead of having to pass the planIds and the subPlansIds
• Check out this other tip on SQL Server 2000 to 2005 Crosswalk - Database Maintenance Plan Wizard to SQL Server Integration Services (SSIS)

About the author

Greg Robidoux is the President and founder of Edgewood Solutions, a technology services company delivering services and solutions for Microsoft SQL Server. He is also one of the co-founders of MSSQLTips.com. Greg has been working with SQL Server since 1999, has authored numerous database-related articles, and delivered several presentations related to SQL Server. Before SQL Server, he worked on many data platforms such as DB2, Oracle, Sybase, and Informix.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips