SQL Server Job Schedule Chart

  • I'm looking for something that does the following:

    Show a chart of recently run jobs, their start times, and duration in a Gantt chart type format. Job name on the Y axis and time on the X axis (in datetime format preferably). I want to be able to have 1 or more duration "blocks" on the x-axis for the job so there's only one row per job.

    I'm looking for either a packaged product that does this or instructions on how to make an RDL that does this.

    The goal is so I have a visual representation of the job schedules in order to easily troubleshoot scheduling conflicts that are going on.

    I'd like it to be SQL version independent, so ideally I would like to be able to feed in my own custom query.

    It's a bit sloppy but my custom query that I'm using now is below:

    DECLARE

    @StartTime DATETIME

    ,@EndTime DATETIME

    ,@SkipJobsUnder INT-- number of seconds to ignore

    SELECT

    @StartTime = '20120207 16:00:00'

    ,@EndTime = '20120208 06:00:00'

    ,@SkipJobsUnder = 60

    SELECT

    sj.name AS JobName

    ,CONVERT(DATETIME, CONVERT(VARCHAR, sjh.run_date) + ' ' + STUFF(STUFF(RIGHT('000000' + CONVERT(VARCHAR, sjh.run_time), 6), 5, 0, ':'), 3, 0, ':')) AS StartTime

    ,DATEADD(

    ss

    ,CONVERT(INT, SUBSTRING(RIGHT('000000' + CONVERT(VARCHAR, sjh.run_duration), 6), 5, 2)) + -- seconds

    CONVERT(INT, SUBSTRING(RIGHT('000000' + CONVERT(VARCHAR, sjh.run_duration), 6), 3, 2)) * 60 + -- minutes

    CONVERT(INT, SUBSTRING(RIGHT('000000' + CONVERT(VARCHAR, sjh.run_duration), 6), 1, 2)) * 60 * 60 -- hours

    ,CONVERT(DATETIME, CONVERT(VARCHAR, sjh.run_date) + ' ' + STUFF(STUFF(RIGHT('000000' + CONVERT(VARCHAR, sjh.run_time), 6), 5, 0, ':'), 3, 0, ':'))

    ) AS StopTime

    ,STUFF(STUFF(RIGHT('000000' + CONVERT(VARCHAR, sjh.run_duration), 6), 5, 0, 'm '), 3, 0, 'h ') + 's' AS RunDuration

    FROM

    sysjobhistory sjh

    JOIN sysjobs sj

    ONsjh.job_id = sj.job_id

    WHERE

    sjh.step_name = '(Job outcome)'

    ANDCONVERT(DATETIME, CONVERT(VARCHAR, sjh.run_date) + ' ' + STUFF(STUFF(RIGHT('000000' + CONVERT(VARCHAR, sjh.run_time), 6), 5, 0, ':'), 3, 0, ':')) BETWEEN @StartTime AND @EndTime

    ANDCONVERT(INT, SUBSTRING(RIGHT('000000' + CONVERT(VARCHAR, sjh.run_duration), 6), 5, 2)) + -- seconds

    CONVERT(INT, SUBSTRING(RIGHT('000000' + CONVERT(VARCHAR, sjh.run_duration), 6), 3, 2)) * 60 + -- minutes

    CONVERT(INT, SUBSTRING(RIGHT('000000' + CONVERT(VARCHAR, sjh.run_duration), 6), 1, 2)) * 60 * 60 > @SkipJobsUnder -- hours

    ORDER BY

    CONVERT(DATETIME, CONVERT(VARCHAR, sjh.run_date) + ' ' + STUFF(STUFF(RIGHT('000000' + CONVERT(VARCHAR, sjh.run_time), 6), 5, 0, ':'), 3, 0, ':')) DESC

    Thanks in advance

    *** Edit - updated the query, it was wrong ***

  • I am on the same mission. Here is my thread from last week with two URLs with free tools

    http://www.sqlservercentral.com/Forums/Topic1243836-391-1.aspx

    I tried both tools. The Idera tool gave me with I was looking for except it seemed inconsistent and or confusing when I had many jobs firing at the same time (which is why I wanted a tool for presentation)

    I liked SQLjobvis too but it really didn't give me exactly what I was looking for.

    SQLJOBVIS

    Let me know what you find!

  • Thank you, I will take a look at these and post again if I find something else that might suit the problem better

  • I really like the visualization of SQLjobvis, was more user-friendly than Idera, and had more options.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply