Get Job Run Detail

Applicability:

SQL Server 2000: Not Suported**

SQL Server 2005: Tested

SQL Server 2008: Tested

SQL Server 2008R2: Tested

SQL Server 2012: Not Tested

Credits:

Author: ChillyDBA

Date: 23 Jul 2012

Description

Retrieves a list of all runs of a specified SQL Server Maintenance Plan scheduled job

Due to the fuzziness of the relationship between the data for Jobs and Maintenance Plans in the MSDB database, the Jobs portion of the data (including extended logging details) has been omitted from this code. I haven't really yet found a way to reliably join the 2 sets of data

** NOTE: This code was written for SQL 2005 and greater. There are slightly different views for obtaining data from SQL 2000, or for obtaining data in SQL 2005+ that has been subjected to an upgrade from SQL 2000. While upgrading maintenance plans generally works, I have always found it beneficial in the long term to recreate them from scratch. The feature set is better, and there are some tasks that are not 100% happy working across editions. I have therefore not included versions of the code that support SQL 2000 version maintenance plans.

Code

Stored Procedure:

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