CODEWITHSUNDEEP
sql-servert-sqlsql-server-2008-r2
SQL_M
SQL_M

Reputation: 2475

Run duration sysjobs - sysjobshistory

I have a question regarding the run_duration of sysjobs. Official docs seem to contradict itself:

https://learn.microsoft.com/en-us/sql/relational-databases/system-tables/dbo-sysjobhistory-transact-sql?view=sql-server-2017

First it specifies: 

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

But then it also mentiones a query for a more friendly time format:

SELECT sj.name,
       sh.run_date,
       sh.step_name,
       STUFF(STUFF(RIGHT(REPLICATE('0', 6) +  CAST(sh.run_time as varchar(6)), 6), 3, 0, ':'), 6, 0, ':') 'run_time',
       STUFF(STUFF(STUFF(RIGHT(REPLICATE('0', 8) + CAST(sh.run_duration as varchar(8)), 8), 3, 0, ':'), 6, 0, ':'), 9, 0, ':') 'run_duration (DD:HH:MM:SS)  '
FROM msdb.dbo.sysjobs sj
JOIN msdb.dbo.sysjobhistory sh
ON sj.job_id = sh.job_id

Notice the difference, days are 'suddenly' brought into the picture.

In my real life example, I came across a job that ran really long. The results are as follows:

run_duration (DD:HH:MM:SS)  run_duration
01:49:39:39                 1493939

So how do I read this? Is this actually 149 hours, 39 minutes and 39 secs?

One day and 49 makes no sense.

Thanks a lot for the feedback!

Upvotes: 0

Views: 1068

Answers (1)

Thom A
Thom A

Reputation: 95830

Normally, if you're using a notation with times, the largest denominator isn't limited; as in that you stop at 24 for hours because that's how many there are in a day. So, in the format HHMMSS, HH can be any value from 0+. the HH isn't limited to 24, as you aren't using a days denominator. Just like if you were counting months you wouldn't stop as 12, even if the difference between 2 dates is 16 months. You're counting in months so why would crossing a year gap stop you.

Like you commented as well 1 day 49 hours literally makes no sense. 1493939 should be read as 149 hours, 39 minutes and 39 seconds.

Upvotes: 2

Related Questions