Avatar of VitaminD
VitaminD
Flag for United States of America asked on

Maintenance SubPlan Information

I have a maintenance plan with 6 Subplans - Each Subplan Runs at a different time
I need to get detailed information about each plan - I need StartDate and EndDate plus other basic metadata for each Subplan- The Query I am running is giving me the same time for each SubPlan - Is there a better way of getting this info - Here is the query I am running


DECLARE @SERVERNAME nvarchar(MAX)
SET @SERVERNAME = @@SERVERNAME
--PRINT @SERVERNAME
DECLARE @SubPlanResults TABLE(SubPlanName VARCHAR(100),PlanName VARCHAR(100),DaysOld INT,
Succeeded BIT,StartTime DATETIME,EndTime DATETIME)
INSERT INTO @SubPlanResults(SubPlanName,PlanName,DaysOld,Succeeded,StartTime,EndTime)
 
SELECT  
SP.Subplan_name, P.name AS PlanName,
--MPL.plan_id, MPL.subplan_id,  
DATEDIFF(DD,MPL.start_time, GETDATE()) AS [Duration(Days)],
MPL.succeeded,
MPL.start_time, MPL.end_time
FROM msdb.dbo.sysmaintplan_log MPL (nolock)
INNER JOIN msdb.dbo.sysmaintplan_plans P (nolock) ON P.id = MPL.plan_id
INNER JOIN msdb.dbo.sysmaintplan_subplans (nolock) SP ON SP.plan_id=P.id
INNER JOIN msdb.dbo.sysmaintplan_logdetail (nolock) LD ON LD.task_detail_id = MPL.task_detail_id
WHERE 0=0
AND SP.subplan_name LIKE '%ReIndex%'
AND DATEDIFF(DD,MPL.start_time, GETDATE()) < 50
--AND MPL.succeeded = 1
ORDER BY MPL.start_time DESC


SELECT * FROM @SubPlanResults


Thanks In Advance
Microsoft SQL Server

Avatar of undefined
Last Comment
EugeneZ

8/22/2022 - Mon
EugeneZ

try this example from http://www.mssqltips.com/sqlservertip/1137/supporting-tables-for-sql-server-2005-style-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
VitaminD

ASKER
The issue I am having is that the Maintenance plan history is not the same as the system tables and views
I cannot get the complete status for each Subplan - The system tables are returning the same startTime and EndTime for all subplans - This cannot be correct because they are all scheduled and run at different times
If anyone has had this issue before or knows how to get detailed log info for each subplan that would be helpful
ASKER CERTIFIED SOLUTION
EugeneZ

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
Your help has saved me hundreds of hours of internet surfing.
fblack61