View for SQL Agent Job Histories

• Download source - 5.3 KB

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

• 20^th October, 2013: Initial public draft
• 7^th 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.
• 12^th March, 2014: [msdb].[dbo].[agent_datetime] -- Thanks to Paw Jershauge
• 30^th May 2014: [msdb].[dbo].[agent_datetime](NULLIF(next_run_date,0),NULLIF(next_run_time,0)) -- Thanks to Henrik