Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

SQL Server Job Schedule Chart Expand / Collapse
Author
Message
Posted Wednesday, February 8, 2012 11:32 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Yesterday @ 1:37 PM
Points: 340, Visits: 817
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
ON sjh.job_id = sj.job_id
WHERE
sjh.step_name = '(Job outcome)'
AND CONVERT(DATETIME, CONVERT(VARCHAR, sjh.run_date) + ' ' + STUFF(STUFF(RIGHT('000000' + CONVERT(VARCHAR, sjh.run_time), 6), 5, 0, ':'), 3, 0, ':')) BETWEEN @StartTime AND @EndTime
AND 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 > @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 ***
Post #1249226
Posted Wednesday, February 8, 2012 1:07 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, September 12, 2014 12:05 PM
Points: 64, Visits: 454
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!
Post #1249292
Posted Thursday, February 9, 2012 6:33 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Yesterday @ 1:37 PM
Points: 340, Visits: 817
Thank you, I will take a look at these and post again if I find something else that might suit the problem better
Post #1249654
Posted Thursday, May 1, 2014 11:14 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: 2 days ago @ 7:32 PM
Points: 40, Visits: 462

I really like the visualization of SQLjobvis, was more user-friendly than Idera, and had more options.
Post #1566918
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse