Click here to Skip to main content
Click here to Skip to main content

View for SQL Agent Job Histories

, 12 Mar 2014
Rate this:
Please Sign up or sign in to vote.
Views to make analyzing SQL Agent Job statistics easier

Introduction

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.

Background

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.

SELECT * FROM [dbo].[VW_JOBS]
 
SELECT * 
FROM [VW_JOBS_HIST] 
WHERE [in_db] = 1 
ORDER BY [sort]

Points of Interest

VW_JOBS_HIST_BASE

SELECT
...
-- 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)
(
    ((run_duration/1000000)*86400) 
    + 
    (((run_duration-((run_duration/1000000)*1000000))/10000)*3600) 
    + 
    (((run_duration-((run_duration/10000)*10000))/100)*60) 
    + 
    (run_duration-(run_duration/100)*100)
) 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

VW_JOBS

SELECT
    -- 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,
min
(
    CASE
    WHEN [is_success] = 1 THEN run_dur_sec
    -- This assumes NULL will not be factored into the aggregation...
    ELSE NULL
    END
) as min_run_dur_success_sec,
 
-- Logic used to compute step checksum
CAST(CHECKSUM
(
    ISNULL([database],'')
    + '|' + ISNULL([job_name],'')
    + '|' + ISNULL([step_name],'')
    + '|' + ISNULL(CAST([step_id] as varchar(32)),'')
    + '|' + ISNULL([step_command],'')
) as bigint) as [checksum_hash]

History

  • 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

License

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

About the Author

Brad Joss
Software Developer (Senior)
United States United States
No Biography provided

Comments and Discussions

 
QuestionFail when job schedule is disabled - and current running job session id's are not found PinmemberMember 1082684519-May-14 10:26 
AnswerRe: Fail when job schedule is disabled - and current running job session id's are not found PinprofessionalBrad Joss30-May-14 5:56 
QuestionGood PinmemberVivek Johari13-Mar-14 17:50 
SuggestionInstead of PinprofessionalPaw Jershauge5-Mar-14 3:24 
GeneralRe: Instead of PinprofessionalBrad Joss11-Mar-14 19:47 
GeneralRe: Instead of PinpremiumPaw Jershauge11-Mar-14 22:58 
Questiona bit slow Pinmemberknow-life-death20-Oct-13 15:54 
AnswerRe: a bit slow PinprofessionalBrad Joss21-Oct-13 7:03 
AnswerRe: a bit slow PinprofessionalBrad Joss21-Oct-13 7:05 

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

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

| Advertise | Privacy | Mobile
Web03 | 2.8.140709.1 | Last Updated 12 Mar 2014
Article Copyright 2013 by Brad Joss
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid