question

ThomasRushton avatar image
ThomasRushton asked

What does sysjobhistory say for very long jobs?

Sorry, just idle curiosity here. I was reading the MSDN documentation (as you do) about the [sysjobhistory][1] table in the MSDB database. It states that the `run_duration` field contains: > Elapsed time in the execution of the job or step in HHMMSS format. And I caught myself wondering what happens if the job is over 24 hours? Given that the `run_time` and `run_date` fields are stored in a bastardised format, I was wondering what would happen here... Anyone got any jobs that run over 24 hours that would care to enlighten me? Or am I going to have to lash something together myself? (Easy enough, but not quick to run, for obvious reasons...) [1]: http://msdn.microsoft.com/en-us/library/ms174997.aspx
msdbjob-historyinsatiable-curiosity
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

FWIW, I currently have two ideas about how to test this, and I'm going to implement them both and blog the results...
1 Like 1 ·
Usman Butt avatar image
Usman Butt answered
It is going to store the run_duration as 245959 and the output through GUI will be like 1.00:59:59 for a job ran for 24 hours 59 minutes and 59 seconds. Cheers. (Please do not ask me how I managed to find it out as I used a dirty trick) :)
5 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

OK, just stretching the point but what if a job runs for 6 days?
1 Like 1 ·
what about if it ran for 26 hours?
0 Likes 0 ·
it will be 260000 and 1.02:00:00 respectively.
0 Likes 0 ·
Its simple. it would be 1440000 and 6.00:00:00 respectively. I guess the whole confusion is due to the consideration that HHMMSS is bound only to 24 hours (in daily life and we do deal in days, years etc). Whereas, we should be interpreting it as the job ran for HH hours (could be any number of hours until it reaches the upper limit of INT. Not to forget last four digits are fixed for MM and ss), MM minutes (max could be 59) and SS seconds (max could be 59 seconds). For instance, if a job ran for 365 hours, the run_duration will be 8760hours(=365days) ,00minutes ,00seconds. If it ran for 365 days, one hour, 32 minutes and 20 seconds, then the run_duration would be 8760hours(=365days) + 1hour, 32minutes, 20seconds i.e.87613220. Hope I am making sense.
0 Likes 0 ·
ah I see, was thinking it was limited to max of 99 hours. not fully concentrating!
0 Likes 0 ·
Fatherjack avatar image
Fatherjack answered
Give me 25 hours and I'll tell you ;)
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.