SQLServerCentral / Article Discussions / Article Discussions by Author / Discuss content posted by webtekkie / Job History with Timeline / Latest Posts

RE: Job History with Timeline

logicinside22 — Tue, 10 Jan 2012 07:29:15 GMT

Nice Script ...I would like to know whats mean of XX in result.i can not see last week's jobs result . like today tue morning i am running this script it showing me result on 2011-01-09 and 2011-01-10 what if i want to see results before this duration?Thanks

RE: Job History with Timeline

amc-885860 — Wed, 02 Jun 2010 13:06:42 GMT

Oh--Ok, I got it now. thank you! Becasue I really like this query. Uh, one more thing...explain LeadingMinutes, please.

RE: Job History with Timeline

webtekkie — Wed, 02 Jun 2010 12:53:01 GMT

Hi amc. It works like a bar chart. For every minute a job is running, an 'X' is printed. For every minute it is not running, a space is printed. You can compare the lines to see when jobs are running at the same time. So, if you have one row returned where the 'X's line up vertically with the lines above or below it, then it means the jobs were running concurrently.Hope that helps,Martin

RE: Job History with Timeline

amc-885860 — Wed, 02 Jun 2010 09:55:20 GMT

I'm a newbie. can you explain a little more about leading minutes and duration timeline column? I don't understand. What spaces are you talking about? Please help me to understand.

RE: Job History with Timeline

Rudy Panigas — Tue, 01 Jun 2010 13:11:42 GMT

Nicely done! I like the graph too. I was looking at the graph and thought shouldn't it reset back to left side of the "Duration Timeline" once the the next day's jobs are executed? Currently it looks like (from the graph) that the times of the jobs are changing.Just a thought.

RE: Job History with Timeline

RML51 — Wed, 13 May 2009 07:10:14 GMT

Thanks, I like it!I did make a couple of minor changes. Our server names are >10 characters and I like to see the run date and run time in descending order.SET DATEFORMAT dmySELECT convert(varchar(20),his.server) as Server, -- Increased to 20 characters convert(varchar(30),job.name) AS job_name, CASE his.run_status WHEN 0 THEN 'Failed' WHEN 1 THEN 'Succeeded' ELSE '???' END as run_status, convert(varchar(4),run_duration/10000) + ':' + convert(varchar(4),run_duration/100%100) + ':' + convert(varchar(4),run_duration%100) as run_duration, convert(datetime, convert(varchar(10),run_date%100) + '/' + convert(varchar(10),run_date/100%100) + '/' + convert(varchar(10),run_date/10000) + ' ' + convert(varchar(4),run_time/10000) + ':' + convert(varchar(4),run_time/100%100) + ':' + convert(varchar(4),run_time%100)) as start_date, datediff(mi, getdate()-1, convert(datetime, convert(varchar(10),run_date%100) + '/' + convert(varchar(10),run_date/100%100) + '/' + convert(varchar(10),run_date/10000) + ' ' + convert(varchar(4),run_time/10000) + ':' + convert(varchar(4),run_time/100%100) + ':' + convert(varchar(4),run_time%100))) as LeadingMinutes, isnull(nullif(convert(int,(run_duration/10000 * 60) + (run_duration/100%100) + (ceiling(run_duration%100/60.0))),0),1) as DurationMinutes, REPLICATE(' ', datediff(mi, getdate()-1, convert(datetime, convert(varchar(10),run_date%100) + '/' + convert(varchar(10),run_date/100%100) + '/' + convert(varchar(10),run_date/10000) + ' ' + convert(varchar(4),run_time/10000) + ':' + convert(varchar(4),run_time/100%100) + ':' + convert(varchar(4),run_time%100)))) + REPLICATE('x', isnull(nullif(convert(int,(run_duration/10000 * 60) + (run_duration/100%100) + (ceiling(run_duration%100/60.0))),0),1)) as DurationTimeline, his.messageFROM msdb.dbo.sysjobhistory his INNER JOIN msdb.dbo.sysjobs job ON his.job_id = job.job_idWHERE convert(datetime, convert(varchar(10),run_date%100) + '/' + convert(varchar(10),run_date/100%100) + '/' + convert(varchar(10),run_date/10000) + ' ' + convert(varchar(4),run_time/10000) + ':' + convert(varchar(4),run_time/100%100) + ':' + convert(varchar(4),run_time%100)) between getdate()-1 and getdate() and step_id = 0ORDER BY his.server, his.run_date DESC, -- Added DESC his.run_time DESC, -- Added DESC job.name

Job History with Timeline

webtekkie — Sun, 05 Apr 2009 17:44:22 GMT

Comments posted to this topic are about the item [B]Job History with Timeline[/B]