Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

MSDB Job History Duration Conversion

Have you ever queried the msdb tables to get duration details about a particular job? I’ve had to do this many times in the past, but never blogged about it and when I needed it recently, I couldn’t find my scripts. I could have done a quick search for what others have done, but I thought I would deep into my brain housing group and see what I can dig up! At any rate, the duration columns often are returned as integer values. The values are not in milliseconds or even seconds and it is not very simple to convert them that way. Let me show you a quick sample query so that you see what I mean.

Script 1: Quick Query to See Run Duration

SELECT run_duration FROM msdb..sysjobhistory 

Figure 1: Results

Results 1 

Looking at the results above, what the run duration is saying for record 1 is that it took 10 minutes and 9 seconds. Record 2 is 6 hours, 12 minutes and 55 seconds. As you can see, it would take quite a bit of  work to get this into seconds. So, what I’ve done below is give you one method of getting duration into a little easier to read format.

Script 2: My Custom Run Duration

SELECT

      h.run_duration

      , CASE LEN(h.run_duration)

            WHEN 1 THEN '00:00:0' + CONVERT(CHAR(1),h.run_duration)

            WHEN 2 THEN '00:00:' + CONVERT(CHAR(2),h.run_duration)

            WHEN 3 THEN '00:0' + CONVERT(CHAR(1),LEFT(h.run_duration,1)) + ':' + CONVERT(CHAR(2),RIGHT(h.run_duration,2))

            WHEN 4 THEN '00:' + CONVERT(CHAR(2),LEFT(h.run_duration,2)) + ':' + CONVERT(CHAR(2),RIGHT(h.run_duration,2))

            WHEN 5 THEN '0' + CONVERT(CHAR(1),LEFT(h.run_duration,1)) + ':' + LEFT(RIGHT(h.run_duration,4),2) + ':' + CONVERT(CHAR(2),RIGHT(h.run_duration,2))

            ELSE

                  CONVERT(VARCHAR(4),LEFT(h.run_duration,LEN(h.run_duration)-4)) + ':' + LEFT(RIGHT(h.run_duration,4),2) + ':' + CONVERT(CHAR(2),RIGHT(h.run_duration,2))

            END AS My_Custom_Run_Duration

FROM

      msdb..sysjobhistory h

Figure 2: Results with My Custom Run Duration

Results 2

I hope that you have enjoyed this quick blog. If you did, please rate it! Also, if you don’t already, please be sure to follow me on twitter at @briankmcdonald.

 

 

Brian K. McDonald, MCDBA, MCSD
Business Intelligence Consultant – Pragmatic Works

Email: bmcdonald@pragmaticworks.com | Blogs: SSRSGeek | SQLServerCentral | BIDN

Twitter: @briankmcdonald

Comments

Posted by dougm128 on 26 March 2011

Most excellent coversion.  Does require a bit of work when incorporating with average run and min and max run times but execellently describes why and what the "run_duration" really depicts.

thank you.

Leave a Comment

Please register or log in to leave a comment.