Supporting tables for SQL Server 2005 style maintenance plans


By:   |   Updated: 2006-12-18   |   Comments   |   Related: More > 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


Last Updated: 2006-12-18


get scripts

next tip button



About the author
MSSQLTips author Greg Robidoux Greg Robidoux is the President of Edgewood Solutions and a co-founder of MSSQLTips.com.

View all my tips
Related Resources





Comments For This Article





download


Recommended Reading

Update Statistics for All Tables and Databases in a SQL Server Instance

Reduce Time for SQL Server Index Rebuilds and Update Statistics

Move SQL Server Maintenance Plan from One Server to Another

SQL Server Maintenance Plan Index Rebuild and Reorganize Tasks

Deleting Historical Data from a Large Highly Concurrent SQL Server Database Table





get free sql tips
agree to terms


Learn more about SQL Server tools