Get Job Run Detail

DROP PROC usp_GetMaintPlanJobRunDetails

GO

CREATE PROC usp_GetMaintPlanJobRunDetails (@Job_ID UNIQUEIDENTIFIER)

AS

/****************************************************

Purpose: To list a single SQL Agent scheduled job and

the historic run details if they exist.

The Job_ID passed as a parameter determines the job

to be displayed.

Job_ID was used ratehr than name as the name can be changed

through the GUI but the Job_ID remains the same,

and it was originally intended that this SP be called

from an SSRS report using click-through to a sub-report (so no need

to actually type the GUID)

Author: ChillyDBA

History: 22 July 2012 - Intial Issue

24 July 2012 - ChillyDBA - removed the joins to the udf_GetMSDBJobList TVF

as the joining between Jobs and Plans executions is a little fuzzy - the plan

history structure has subplan_id and task_detail_id as the keys, whereas jobs have

job_id and step_uid. Had to settle for just getting all the task details related to one

job_id. There is no real method of retrieving the extended logging for a maintplan task

that I could find.

*****************************************************/

SELECT

sp.subplan_name AS SubPlanName,

pld.line1 AS SubPlanTaskName,

pld.line2 AS PlanTaskDescription2,

pld.line3 AS PlanTaskDescription3,

pld.line4 AS PlanTaskDescription4,

pld.line5 AS PlanTaskDescription5,

pld.Start_Time AS PlanTaskStartTime,

pld.End_Time AS PlanTaskEndTime,

DATEDIFF(ss, pld.Start_Time, pld.End_Time) AS PlanTaskDuration,

CASE pld.succeeded

WHEN 1 THEN 'Succeeded'

ELSE 'Failed'

END AS PlanTaskStatus,

pld.error_number AS PlanErrorNumber,

pld.error_message AS PlanErrorMessage

FROM msdb.dbo.sysmaintplan_subplans sp

INNER JOIN msdb.dbo.sysmaintplan_log pl

ON sp.subplan_id = pl.subplan_id

INNER JOIN msdb.dbo.sysmaintplan_logdetail pld

ON pl.task_detail_id = pld.task_detail_id

WHERE sp.job_id = @job_id

ORDER BY

pld.Start_Time,

pld.line1