Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Scripts          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Supporting tables for SQL Server 2005 style maintenance plans


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




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Learn more about SQL Server tools