SQL Job Steps And Run_Duration: The query that formats run_duration correctly and doesn’t make you want to shoot your eye out


Ever tried to figure out how long a step for a particular SQL Server job takes? You can of course get it from the Management Studio, but we’re database people! We want to see the trend over time not just a point in time! We want to write queries! Hooray for data!

You do a little digging, and find out the information you need is stored in the MSDB sysjobhistory. And look! There’s even a run_duration column! How easy is this! Uh, wait a second… That duration looks a little funny. It turns out run_duration data is being stored in a HHMMSS format. Why? I don’t know but it sure makes it hard to use.

"I'd rather shoot my eye out than leave run_duration formatted like that!"
“I’d rather shoot my eye out than leave run_duration formatted like that!”

Well, lucky for you I smashed through, hundreds, no thousands, perhaps millions of newsgroup articles to bring you the cream of the crop, the best of the best, step history query. This is the official Red Ryder, carbine action, two-hundred shot range model air rifle of job step history queries!


SELECT  j.name JobName
      , h.step_name StepName
      , CAST(STR(h.run_date, 8, 0) AS DATETIME)
        + CAST(STUFF(STUFF(RIGHT('000000' + CAST (h.run_time AS VARCHAR(6)), 6),
                           5, 0, ':'), 3, 0, ':') AS DATETIME) AS StartDatetime
      , DATEADD(SECOND,
                ( ( h.run_duration / 1000000 ) * 86400 )
                + ( ( ( h.run_duration - ( ( h.run_duration / 1000000 )
                                           * 1000000 ) ) / 10000 ) * 3600 )
                + ( ( ( h.run_duration - ( ( h.run_duration / 10000 ) * 10000 ) )
                      / 100 ) * 60 ) + ( h.run_duration - ( h.run_duration
                                                            / 100 ) * 100 ),
                CAST(STR(h.run_date, 8, 0) AS DATETIME)
                + CAST(STUFF(STUFF(RIGHT('000000'
                                         + CAST (h.run_time AS VARCHAR(6)), 6),
                                   5, 0, ':'), 3, 0, ':') AS DATETIME)) AS EndDatetime
      , STUFF(STUFF(REPLACE(STR(run_duration, 6, 0), ' ', '0'), 3, 0, ':'), 6,
              0, ':') AS run_duration_formatted
      , ( ( h.run_duration / 1000000 ) * 86400 ) + ( ( ( h.run_duration
                                                         - ( ( h.run_duration
                                                              / 1000000 )
                                                             * 1000000 ) )
                                                       / 10000 ) * 3600 )
        + ( ( ( h.run_duration - ( ( h.run_duration / 10000 ) * 10000 ) )
              / 100 ) * 60 ) + ( h.run_duration - ( h.run_duration / 100 )
                                 * 100 ) AS RunDurationInSeconds
      , CASE h.run_status
          WHEN 0 THEN 'failed'
          WHEN 1 THEN 'Succeded'
          WHEN 2 THEN 'Retry'
          WHEN 3 THEN 'Cancelled'
          WHEN 4 THEN 'In Progress'
        END AS ExecutionStatus
      , h.message MessageGenerated
FROM    sysjobhistory h
        INNER JOIN sysjobs j
            ON j.job_id = h.job_id
ORDER BY h.run_date DESC
      , h.run_time desc

 

 


Leave a Reply

Your email address will not be published.