sysjobhistory run_duration

  • Is this in seconds or ? It is not documented in BOL except for amount of time incurred in execution of a job. Thanks ...

  • From SQL Server 2000 System Table Map (download from MS - search on systbl.chm)

    "Elapsed time in the execution of the job or step in HHMMSS format."

    Hope this helps.

    David

    David

    @SQLTentmaker

    “He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot

  • It is based on time value sections.

    Ex.

    run_duration = HH:MM:SS

    1 = 00:00:01

    335 = 00:03:35

    102456 = 10:24:56

    Hops this helps.

    "Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)

  • Antares686 Thanks for your assistance on this matter, pochinej from http://www.sql-scripting.com

  • DavidB Thanks also, I have the sytbl map and was working sql7 never know if its been changed but seems it has not been, Thanks...

  • All,

    I hate the formatting myself so thought I would share. This code was "borrowed" from Mike Pearson, thanks Mike...

    As you can see it's all in the CASE.

    select j.name,

    case when h.step_name ='(Job outcome)' then '(Package Total)' else h.step_name end

    , h.run_status,

    CASE len(h.run_duration)

    WHEN 1 THEN cast('00:00:0'

    + cast(h.run_duration as char) as char (8))

    WHEN 2 THEN cast('00:00:'

    + cast(h.run_duration as char) as char (8))

    WHEN 3 THEN cast('00:0'

    + Left(right(h.run_duration,3),1)

    +':' + right(h.run_duration,2) as char (8))

    WHEN 4 THEN cast('00:'

    + Left(right(h.run_duration,4),2)

    +':' + right(h.run_duration,2) as char (8))

    WHEN 5 THEN cast('0'

    + Left(right(h.run_duration,5),1)

    +':' + Left(right(h.run_duration,4),2)

    +':' + right(h.run_duration,2) as char (8))

    WHEN 6 THEN cast(Left(right(h.run_duration,6),2)

    +':' + Left(right(h.run_duration,4),2)

    +':' + right(h.run_duration,2) as char (8))

    END as 'Duration'

    ,h.run_duration

    from dbo.sysjobs j

    inner join dbo.sysjobhistory h

    on j.job_id=h.job_id

    where j.name like '%somejobName%'

    order by j.name, h.step_name

  • One more solution without case statement found in another forum,

    SELECT stuff(stuff(replace(str(run_duration,6,0),' ','0'),3,0,':'),6,0,':') FROM sysJobHistory

  • Just curious in a hypothetical extreme situation but what if a job runs beyond a day like say for example 25 hours (or further more weeks, months, years, etc...)?...would it then still be represented as 25 hours or something like 1 day & 1 hour? Thanks in advance.

  • Yes, days can be included on the value. You can test this by creating a job that just sits for a day. I have a messy formula to calculate the value.

    I like to deal with it in seconds so I use this:

    ((jh.run_duration/1000000)*86400) + (((jh.run_duration-((jh.run_duration/1000000)*1000000))/10000)*3600) + (((jh.run_duration-((jh.run_duration/10000)*10000))/100)*60) + (jh.run_duration-(jh.run_duration/100)*100)

    Where jh is the jobhistory table..

    CEWII

  • Thanks Elliott for the quick reply, much appreciated. So the further left/highest value that the run_duration field would contain would always be displayed in hours then?

  • No, as I remember and my formula suggests a day would be represented as 1000000, this is handled by ((jh.run_duration/1000000)*86400).

    I never tested a case where a year had passed and I think it unlikely to happen.

    CEWII

  • Apologies for bringing up an ancient thread, but when I wanted the start and end times the simplest solution that I have found (in SQL Server 2019) is to use the FORMAT() function and add it to the start time.

    select JobName = j.[name]
    ,StartTime = msdb.dbo.agent_datetime(run_date, run_time)
    ,EndTime = msdb.dbo.agent_datetime(run_date, run_time)
    + format(h.run_duration,'00:00:00')
    From msdb.dbo.sysjobs j
    INNER JOIN msdb.dbo.sysjobhistory h
    ON j.job_id = h.job_id

    • This reply was modified 8 months, 3 weeks ago by  MrRimmer.

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply