June 12, 2018 at 10:21 am
Hi there, I've got a couple of quick questions about the sysjobhistory table in msdb which I can't find satisfactory answers to on the web anywhere.
Firstly the run_status field, most pertinently status 4 the "In Progress" status...
I noticed that whilst running a "normal" SQL Job no entries seem to get put into the sysjobhistory table until a step has completed, so I wasn't clear when an entry could be added with the In Progress state. I then realized that entries in sysjobhistory with this status are steps for those jobs that run continuously and repeatedly e.g. Replication and CDC jobs. Is there a definitive list of what types of steps will be entered into the sysjobhistory table with this status?
Secondly the run_duration field is a bit puzzling. According to all documentation I can find this is in format HHMMSS and this seems to be perfectly correct and understandable 99.99% of the time. However long running jobs that last over four days will necessarily need to go to seven figures - is it more accurate to say that this field is then in HHHMMSS format (as it looks like from the few examples I can find) or is it in DHHMMSS format?
Finally, almost a combination of the two questions above...
I have noticed that for CDC job steps in sysjobhistory the run_duration field does not seem to be in HHMMSS format at all. We have values in the seconds and minutes positions that are greater than 60. This doesn't really make sense at all as I have consecutive entries for the same job step that go, for example, from 14596 then 14601 - taking the format at face value and multiplying the minutes up to get a grand total in seconds would mean that this step's run duration was getting smaller as time went on. So I guess the question/assumption is - is the run_duration field for these types of step simply a total of seconds and not in HHMMSS format at all? If so, what types of steps would be in this format rather than the standard HHMMSS format? Just CDC? Any steps that are in status "In Progress" (didn't seem to be the case for replication steps that I looked at)?
Any help would be appreciated!
Thanks.
June 12, 2018 at 11:37 am
The run_duration column isn't actually a "time" datatype, it's an INT. So there's some weird query gymnastics you have to go through to get it from something like 243834 to the actual time.
Below is the code to at least get the colons in place:
LEFT(RIGHT('000000' + CAST(msdb.dbo.sysjobhistory.run_duration AS VARCHAR(6)), 6),
2) + ':' + SUBSTRING(RIGHT('000000'
+ CAST(msdb.dbo.sysjobhistory.run_duration AS VARCHAR(6)),
6), 3, 2) + ':'
+ RIGHT(RIGHT('000000' + CAST(msdb.dbo.sysjobhistory.run_duration AS VARCHAR(6)),
6), 2)
The MS Docs page has something different:STUFF(STUFF(STUFF(RIGHT(REPLICATE('0', 8) + CAST(msdb.dbo.sysjobhistory.run_duration as varchar(8)), 8), 3, 0, ':'), 6, 0, ':'), 9, 0, ':') 'run_duration (DD:HH:MM:SS) '
June 12, 2018 at 11:47 am
I tend to use this formula to convert run_duration into seconds and work with it from there:FLOOR(run_duration/10000) * 3600 + FLOOR(run_duration/100%100) * 60 + run_duration%100
I've never worked with CDC and I haven't seen the run_duration with parts over 60 as you've described.
June 12, 2018 at 12:59 pm
1) No, afaik.
2) Yes, [H]HHHMMSS.
3) I've no longer got CDC jobs really, but I don't remember seeing that phenomenon when I did, very interesting.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply