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
Query 1 - display a list of maintenance plans
SELECT s.name
FROM msdb.dbo.sysmaintplan_plan
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_plan
INNER JOIN msdb.dbo.sysmaintplan_subp
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_plan
INNER JOIN msdb.dbo.sysmaintplan_subp
INNER JOIN msdb.dbo.sysmaintplan_log AS spl ON spl.subplan_id=sp.subplan_
WHERE sp.subplan_id=N'f9f27b1c-b
AND s.id=N'a8a6cbd6-6d58-453b-
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_plan
INNER JOIN msdb.dbo.sysmaintplan_subp
INNER JOIN msdb.dbo.sysmaintplan_log AS spl ON spl.subplan_id=sp.subplan_
INNER JOIN msdb.dbo.sysmaintplan_logd
WHERE (sp.subplan_id=N'f9f27b1c-
AND(s.id=N'a8a6cbd6-6d58-4
ORDER BY [StartTime] DESC