Supporting tables for SQL Server 2005 style maintenance plans
By: Greg Robidoux | Comments | Related: More > Maintenance
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?
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:
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)
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.
- 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
View all my tips