February 8, 2012 at 11:32 am
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 ***
February 8, 2012 at 1:07 pm
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.
Let me know what you find!
February 9, 2012 at 6:33 am
Thank you, I will take a look at these and post again if I find something else that might suit the problem better
May 1, 2014 at 11:14 pm
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