Click here to Skip to main content
15,611,312 members
Articles / Database Development / SQL Server
Posted 20 Oct 2013

Tagged as


9 bookmarked

View for SQL Agent Job Histories

Rate me:
Please Sign up or sign in to vote.
4.50/5 (4 votes)
12 Mar 2014CPOL2 min read
Views to make analyzing SQL Agent Job statistics easier


These 3 views work together to aggregate and simplify SQL Agent Job execution details. They are useful for analyzing job interaction patterns (like overlapping job steps), performance details, or just to get a quick peek at what SQL Agent Jobs are doing.


There are 3 views in this solution:

VW_JOBS_HIST_BASE: A "private" view used by the other two as a basis for historical information.

VW_JOBS: Displays one row for each job step, aggregating historical information in line. Key result columns include:

  • Database Name
  • Bit flag indicating if job will run against currently selected DB
  • Job and Step Names and IDs
  • Job created and modified dates
  • Bit flag indicating if the job is enabled
  • Step command
  • First, last and next execution times
  • Last execution runtime in seconds and formatted as HH:MM:SS
  • Last execution status and message(s)
  • Number of schedules created for this job
  • Execution total, success and fail counts
  • Min, Max, Average and Median runtime durations in seconds and formatted as HH:MM:SS
  • Min, Max and Average successful-only runtime durations in seconds and formatted as HH:MM:SS
  • If job step is currently running, running Session ID, status and program name
  • Checksum of key job details, including Job, Step and DB names, Step Command and step sequence ID

VW_JOBS_HIST: Displays one row for each job step execution.

  • Database Name
  • Bit flag indicating if job will run against currently selected DB
  • Job and Step Names and IDs
  • Job created and modified dates
  • Bit flag indicating if the job is enabled
  • Step start and end times
  • Step run time in seconds and formatted as HH:MM:SS
  • Step results including status code, status details, messages and success indicator bit
  • Step command
  • Notification details
  • Default sort order to make order by easier

Using the Code

Use the code as may be meaningful to you. Basic usage is as follows, but you can export results to Microsoft Excel for deep analysis or call via job to gather statistics over time, among other things.

WHERE [in_db] = 1 
ORDER BY [sort]

Points of Interest


-- Thanks to Paw Jershauge for recommending msdb.dbo.agent_datetime
[msdb].[dbo].[agent_datetime](next_run_date,next_run_time) as [next_run_time],
-- Convert runtime duration to seconds (code copied from web)
) as [run_dur_sec],
-- Switch status to text
case run_status 
    when 0 then 'Failed'
    when 1 then 'Succeeded' 
    when 2 then 'Retry' 
    when 3 then 'Cancelled' 
    when 4 then 'In Progress' 
end as [run_status_desc],
-- Define what "success" means for any given execution so later we can write queries that say
-- show me the queries that were successful, or moreover, show me average runtimes of successful
-- queries, ignoring failures.
CAST(case run_status 
    when 0 then 0 --'Failed'
    when 1 then 1 --'Succeeded' 
    when 2 then 0 --'Retry' 
    when 3 then 0 --'Cancelled' 
    when 4 then NULL -- 'In Progress' -- In progress, we don't know
end as [bit]) as [is_success],
FROM msdb..sysjobhistory


    -- Format a value so if the job is currently running, we can match the job and step name
    -- as it would appear in dm_exec_sessions
    'SQLAgent - TSQL JobStep (Job ' 
        + ISNULL(CONVERT(varchar(2000), cast(j.job_id as binary(16)), 1 ),'NULL') 
        + ' : Step ' 
        + CAST(sjs.step_id as varchar(2000)) 
        + ')' as [step_program_name]
FROM msdb..sysjobs j
INNER JOIN msdb..sysjobsteps  sjs
    ON sjs.job_id = j.job_id
LEFT OUTER JOIN sys.dm_exec_sessions ssn
    on (ssn.[program_name] = cte.[step_program_name])

-- Sample of formatting structure to format second as HH:MM:SS
CONVERT(varchar(8), DATEADD(ss, DATETIME, '1/1/2000'), 108) as [min_run_dur_hhmmss],

-- Sample of computation difference between Average (and Min and Max) versus successful Average
min(run_dur_sec) as min_run_dur_sec,
    WHEN [is_success] = 1 THEN run_dur_sec
    -- This assumes NULL will not be factored into the aggregation...
) as min_run_dur_success_sec,
-- Logic used to compute step checksum
    + '|' + ISNULL([job_name],'')
    + '|' + ISNULL([step_name],'')
    + '|' + ISNULL(CAST([step_id] as varchar(32)),'')
    + '|' + ISNULL([step_command],'')
) as bigint) as [checksum_hash]


  • 20th October, 2013: Initial public draft
  • 7th January, 2014: Bug fix. If a job step has always been failing (never had a successful run), it was not appearing in the results. Bug fix checks for this condition and addresses it.
  • 12th March, 2014: [msdb].[dbo].[agent_datetime] -- Thanks to Paw Jershauge
  • 30th May 2014: [msdb].[dbo].[agent_datetime](NULLIF(next_run_date,0),NULLIF(next_run_time,0)) -- Thanks to Henrik


This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Written By
Software Developer (Senior)
United States United States
This member has not yet provided a Biography. Assume it's interesting and varied, and probably something to do with programming.

Comments and Discussions

QuestionFail when job schedule is disabled - and current running job session id's are not found Pin
Henrik Engmark19-May-14 10:26
Henrik Engmark19-May-14 10:26 
Hej Brad

I like the views - but it fails if a job schedule is disabled.

This happens when calling the function [msdb].[dbo].[agent_datetime] where first parameter is zero.

If I change the code to:

[msdb].[dbo].[agent_datetime]((case next_run_date when 0 then null else next_run_date end),next_run_time) as [next_run_time]

Then it will not fail, and return NULL for Next_Exec.

The other problem is that it will not return session id of a running job. The problem is that ssn.program_name is NULL. I have no solution for that right now.

AnswerRe: Fail when job schedule is disabled - and current running job session id's are not found Pin
Brad Joss30-May-14 5:56
professionalBrad Joss30-May-14 5:56 
QuestionGood Pin
Vivek Johari13-Mar-14 17:50
Vivek Johari13-Mar-14 17:50 
SuggestionInstead of Pin
Paw Jershauge5-Mar-14 3:24
Paw Jershauge5-Mar-14 3:24 
GeneralRe: Instead of Pin
Brad Joss11-Mar-14 19:47
professionalBrad Joss11-Mar-14 19:47 
GeneralRe: Instead of Pin
Paw Jershauge11-Mar-14 22:58
Paw Jershauge11-Mar-14 22:58 
Questiona bit slow Pin
know-life-death20-Oct-13 15:54
know-life-death20-Oct-13 15:54 
AnswerRe: a bit slow Pin
Brad Joss21-Oct-13 7:03
professionalBrad Joss21-Oct-13 7:03 
AnswerRe: a bit slow Pin
Brad Joss21-Oct-13 7:05
professionalBrad Joss21-Oct-13 7:05 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.