Job Agent Execution (Run) Timeline Report Graph in SSRS

10 Nov
November 10, 2012

Being able to holistically view your SQL Server Job Agent job schedules, how long each job took to run, what other jobs were running at the same time and the status of each job, should be a fundamental part of Job Agent management.

In this post I will be briefly introducing the current SSRS reports integrated into SSMS (or SQL Data Tools) that displays SQL Server Job Agent job information, as well as provide a new report (pictures above) that should give you a better holistic view of activities on your SQL Server Agent, as well as highlight any conflict in job schedules or times were no activity is occurring.

Currently SQL Server ships with 2 SSRS reports that displays some information about job run time and status, these reports can be reached via SSMS or Data Tools by pressing Right-Click on the SQL Server Agent node in Object Explorer and then clicking on Reports, were you will see two reports under Standard Reports:

  • Job Steps Execution History: Shows historical performance of each job, along with execution duration and returned status.
  • Top Jobs: Shows the top 20 most frequently executed, most frequently failing and slowest jobs. Handy for targeting the worst performing jobs

There is also the Job Activity Monitor Form: This is generally what most people use to monitor the activity on their SQL Server Agent instance, such as job failure, historical execution time, and many other textual data.

These reports are basic and provides a nice way to start investigating performance and execution problems within your Job Agent, but they are oriented around jobs, duration and execution statuses, rather than the visualization of temporal data in a way that we can detect jobs that are overlapping, the ones that are conflicting with each other, and generally the distribution of jobs over time to help you detect any periods of low activity and high activity on your Job Agent instance.

For that reason I created the SQL Job Agent Schedule Timeline SSRS report. This report (pictured at the top of the article) is essentially a Range Bar SSRS Chart that displays Time on its x-axis and jobs on the y-axis, ordered by the time when the execution started, the report takes the following parameters:

  • Server Name: The name of the server were the Job Agent instance resides, in the format of [Server Name]\[Instance Name]
  • Days Back: How many days should the report display. This parameter also determines the size of the report, which is dynamically increased if more days are displayed to accommodate for the increased number of jobs.

The SSRS report uses Job Agent System Tables to grab historical job run information, the query for which is embedded within the report itself (so no need to create a stored procedure on the server). The report is setup to run with the security context of the current Windows user, which needs to have rights to read from the Job Agent System Tables in msdb. Only jobs that took longer than 5 minutes to execute are displayed (limited by the query in the RDL), I only added that because otherwise the graph becomes very messy on my servers, this restriction can be removed by editing the query.

The report will display a job’s timeline in a blue bar if the job did not report a status 0 (or failure), and a red bar will be shown if the status is 0.

Although it is not really very much, it gives a very nice overview of how jobs are running on the current server, and helps you detect in a very visual way any issues that might rise due to schedule conflicts, high concentration of jobs at specific times of day, and time opportunities of low activities to move jobs into for a less resource intensive schedule.

Job Agent Execution (Run) Timeline Report Graph in SSRS rated 3 out of 5 by 4 readers

Job Agent Execution (Run) Timeline Report Graph in SSRS , 3.0 out of 5 based on 4 ratings
* * * * ½ 3 votes
Tags: , , , , ,
5 replies
  1. eldritch-fossicker says:

    Thanks for sharing thought I would do the same. I ran into a snag with jobs that execute longer than 24hr. Some other changes to match my coding style.

    Reply
  2. eldritch-fossicker says:

    err removed my link
    code dump:
    SELECT
    name
    — added Days Back to use a fitler aginst the user input
    ,DATEDIFF(DAY,CONVERT(DATE, calc.run_date),GETDATE()) AS DaysBack
    ,CONVERT( DATETIME, calc.run_date + SPACE(1) + calc.run_time) AS start_time
    — using seconds here as I had a problem with times over 24 hrs
    ,DATEADD(SECOND, jh.run_duration, CONVERT( DATETIME, calc.run_date + SPACE(1) + calc.run_time)) AS end_time
    ,run_status
    ,instance_id
    FROM msdb.dbo.sysjobhistory jh
    INNER JOIN msdb.dbo.sysjobs j ON jh.job_id = j.job_id
    — you where using TIME before so i’m assuming SQL 2008+
    CROSS APPLY (
    SELECT
    — using brittle padding, we know the source is a number
    — stuffing in symbols from back to front just a preference
    STUFF(STUFF(REPLACE(STR(jh.run_date, 8), SPACE(1), ‘0’),7,0,’-‘),5,0,’-‘) AS run_date,
    STUFF(STUFF(REPLACE(STR(jh.run_time,6),SPACE(1),’0′) ,5,0,':’),3,0,':’) AS run_time,
    STUFF(STUFF(REPLACE(STR(jh.run_duration,6),SPACE(1),’0′),5,0,':’),3,0,':’) AS run_duration
    ) AS calc
    WHERE
    step_id = 0
    — limit query to only last week and remove dynamic filter, I have a small setup
    AND jh.run_date >= CONVERT(INT, FORMAT(DATEADD(DAY, -7, GETDATE()), ‘yyyyMMdd’, ‘en-GB’))
    — just using a magic number here for simplicity, 05 minutes 00 seconds
    AND jh.run_duration > 500
    ORDER BY
    start_time

    Reply

Leave a Reply

Want to join the discussion?
Feel free to contribute!

Leave a Reply to eldritch-fossicker Cancel reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>