Click here to Skip to main content
15,561,125 members
Articles / Database Development / SQL Server
Tip/Trick
Posted 20 Oct 2013

Tagged as

Stats

19.9K views
144 downloads
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

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.

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

Points of Interest

VW_JOBS_HIST_BASE

SQL
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

SQL
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)


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 11:26
Henrik Engmark19-May-14 11:26 
AnswerRe: Fail when job schedule is disabled - and current running job session id's are not found Pin
Brad Joss30-May-14 6:56
professionalBrad Joss30-May-14 6:56 
QuestionGood Pin
Vivek Johari13-Mar-14 18:50
Vivek Johari13-Mar-14 18:50 
SuggestionInstead of Pin
Paw Jershauge5-Mar-14 4:24
Paw Jershauge5-Mar-14 4:24 
GeneralRe: Instead of Pin
Brad Joss11-Mar-14 20:47
professionalBrad Joss11-Mar-14 20:47 
GeneralRe: Instead of Pin
Paw Jershauge11-Mar-14 23:58
Paw Jershauge11-Mar-14 23:58 
Questiona bit slow Pin
know-life-death20-Oct-13 16:54
know-life-death20-Oct-13 16:54 
AnswerRe: a bit slow Pin
Brad Joss21-Oct-13 8:03
professionalBrad Joss21-Oct-13 8:03 
AnswerRe: a bit slow Pin
Brad Joss21-Oct-13 8:05
professionalBrad Joss21-Oct-13 8: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.