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

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

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