Get MSDB Job Last Run Summary

DROP PROC usp_GetMaintPlanJobLastRunSummary

GO

CREATE PROC usp_GetMaintPlanJobLastRunSummary

AS

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

Purpose: To list all SQL Maintenance Plan jobs

scheduled using SQL Agent and

the last run details if they exist.

Utilised the following 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: This SP is basically the usp_GetMSDBJobLastRunSummary sp with an additional join

via the sysmaintplan_subplans table to screen out all non-MaintPlan jobs.

Author: ChillyDBA

History: 23 July 2012 - Intial Issue

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

;WITH LastRun AS

(

SELECT

jh.job_id AS Job_ID,

MAX(jh.instance_id) AS Instance_ID

FROM msdb.dbo.sysjobhistory jh (NOLOCK)

WHERE jh.step_id = 0 --'(Job OutCome)'

GROUP BY jh.job_id

)

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.sql_message_id AS JobSQLMessageID,

jh.sql_severity AS JobSQLSeverity,

jh.MESSAGE AS JobMessage,

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

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,

soe.name AS OperatorEmailed,

son.name AS OperatorNetSent,

sop.name AS OperatorPaged,

jh.server AS ServerName

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

-- NOTE: we can join directly to subplans as this is the first table in the maintplan storage

-- that contains a link to SQLAgent jobs. This ensures only MaintPlan jobs are returned in the results

INNER JOIN msdb.dbo.sysmaintplan_subplans sp

ON jobs.job_id = sp.job_id

LEFT OUTER JOIN LastRun lr

ON jobs.job_id = lr.job_id

LEFT OUTER JOIN msdb.dbo.sysjobhistory jh (NOLOCK)

ON lr.job_id = jh.job_id

AND lr.Instance_ID = jh.Instance_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