Wednesday, November 5, 2014

Get SQL Agent Job History Durations in Units of Seconds

For some reason, the run_duration column of the sysjobhistory system table is stored as an integer in HHMMSS format. I don't know who thought this was a good idea, but here's some code to list all your Jobs' history, with their duration in units of seconds.

-- Note that, hilariously, "run_duration" is an INT in HHMMSS format. 
use msdb
set nocount on

  select jh.server                                             as 'Server Name'
       , convert(datetime, convert(varchar(8), jh.run_date))   as 'Run Date'
       , sum(jh.run_duration / 10000 * 3600
       + jh.run_duration % 10000 / 100 * 60
       + jh.run_duration % 100)                                as 'Duration Seconds'
    from sysjobs         j
    join sysjobhistory   jh
      on j.job_id = jh.job_id
   where j.enabled = 1
     and jh.step_id = 1
     and jh.run_status = 1
group by jh.server
       , jh.run_date
order by jh.run_date
       , jh.server