Get MSDB Job Run Details

DROP PROC usp_GetMSDBJobRunDetails

GO

CREATE PROC usp_GetMSDBJobRunDetails (@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)

Utilised the followng function(s) to retrieve

results and/or to enhance readability:

dbo.udf_GetMSDBJobList - gets a basic set of job details

dbo.udf_IntToDateTime - converts the MSDB date and time integers into a datetime variable

NOTE: All steps (0..n) will be return for each job, as there may be several combinations of results type

which would be complex to code for, but which would make sense if displayed as a report grouping

1. Job failed on invocation - only step #0 with fail status

2. Job failed during invocation - steps 0..n with fail status for steps #0 and #n and success for steps in between

3. Job succeeded - steps 0..n all with succeeded status

Unfortunately, step #0 contains summary information - for single-step jobs, step #0 and #1 appear to have duplicate info.

Author: ChillyDBA

History: 22 July 2012 - Intial Issue

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

SELECT

jobs.Job_ID AS Job_ID,

jobs.JobName AS JobName,

jobs.Category AS JobCategory,

jobs.Enabled AS JobEnabled,

jobs.Scheduled AS JobScheduled,

jobs.Description AS JobDescription,

jobs.Occurs AS JobOccurs,

jobs.OccursDetail AS JobOccursDetail,

jobs.Frequency AS JobFrequency,

jobs.Duration AS JobDuration,

jobs.JobValidUntil AS JobValidUntil,

jh.step_id AS JobStepID,

dbo.udf_IntToDateTime(jh.run_date, jh.run_time) AS JobLastRunDate,

jh.run_duration AS JobLastRunDuration,

CASE jh.run_status

WHEN 0 THEN 'Failed'

WHEN 1 THEN 'Succeeded'

WHEN 2 THEN 'Retry'

WHEN 3 THEN 'Cancelled'

END AS JobLastRunStatus,

jh.sql_message_id AS JobSQLMessageID,

jh.sql_severity AS JobSQLSeverity,

jh.MESSAGE AS JobMessage,

soe.name AS OperatorEmailed,

son.name AS OperatorNetSent,

sop.name AS OperatorPaged,

jh.server AS ServerName

FROM dbo.udf_GetMSDBJobList (NULL, 'A') jobs

INNER JOIN msdb.dbo.sysjobhistory jh (NOLOCK)

ON jobs.job_id = jh.job_id

LEFT OUTER JOIN msdb..sysoperators soe

ON jh.operator_id_emailed = soe.id

LEFT OUTER JOIN msdb..sysoperators son

ON jh.operator_id_netsent = son.id

LEFT OUTER JOIN msdb..sysoperators sop

ON jh.operator_id_paged = sop.id

WHERE jobs.job_id = @job_id

ORDER BY jh.instance_id DESC -- list the runs from the most recent to the least recent