SQL: Job Results - Last Execution Info
Author: jason
Date: 2014-03-20 00:00:00
Category: Musing

Here is a script for viewing Job History of how a job ran the last time it ran. Note, I am avoiding dbo.sp_help_jobhistory. That procedure is nice, it gets all the history. But it can be confusing because each step is listed, including the Job Outcome Step, and that's not entirely clear...

Below I use a Common Table Expression, the OVER clause, and a Table Value Constructor




Here is the code
Code

WITH CTE AS (
SELECT instance_id, job_id, ROW_NUMBER() OVER (PARTITION BY
job_id ORDER BY job_id, instance_id DESC) RowNum
FROM msdb.dbo.sysjobhistory WITH(NOLOCK)
) SELECT h.Server, c.Instance_ID, j.Job_ID, j.name JobName, e.Enabled
--, h.step_name StepName, h.step_id StepID
, t.RunStatus, t.RunColor, h.message JobMessage, h.run_date RunDate
, h.run_time RunTime, h.run_duration RunDuration
, a.next_scheduled_run_date NextRunDate
FROM CTE c
INNER JOIN msdb.dbo.sysjobs j WITH(NOLOCK) ON j.job_id = c.job_id
INNER JOIN msdb.dbo.sysjobhistory h WITH(NOLOCK)
ON h.instance_id = c.instance_id AND h.job_id = c.job_id
LEFT JOIN msdb.dbo.sysjobactivity a WITH(NOLOCK)
ON a.job_history_id = c.instance_id AND a.job_id = c.job_id
INNER JOIN (VALUES (1, 'Yes'), (0, 'No')) AS e(EnabledID,Enabled)
ON e.EnabledID = j.enabled
INNER JOIN (VALUES
(0, 'Failed', 'Red'),(1, 'Succeeded', 'Green')
,(2, 'Retry', 'Amber'),(3, 'Canceled', 'Amber'),(4, 'Running', 'Green')
) AS t(RunStatusID,RunStatus,RunColor)
ON t.RunStatusID = h.run_status
WHERE c.RowNum = 1



Here is the output of SP_Help_JobHistory
Code

EXEC dbo.sp_help_jobhistory;






If you want to combine the Run Date and Run Time into a DateTime, use the following function: msdb.dbo.agent_datetime(run_date, run_time)

Job History
http://technet.microsoft.com/en-us/library/ms181046.aspx

Table Value Constructor
http://technet.microsoft.com/en-us/library/dd776382.aspx

Common Table Expression
http://technet.microsoft.com/en-us/library/ms190766(v=sql.105).aspx

OVER Clause
http://technet.microsoft.com/en-us/library/ms189461.aspx

MSDB Date Time Function
http://beyondrelational.com/modules/2/blogs/70/posts/15494/agentdatetime-function-from-msdb-database.aspx



jason @ jasonthomasfrance.com - www.masterstationlog.com - copyright 2009