T-SQL : Using AGENT_DATETIME with SYSJOBHISTORY

In the course of working with the DR, you’re going to wind up setting up scheduled jobs through SQL Agent, and – inevitably – you’re going to want to calculate job durations to see what is running long, perhaps unexpectedly so.

This leads you to the msdb.dbo.sysjobhistory table, which tracks the start and stop of each job instance. But in that table, when you look at the start and stop date/times, you find that Microsoft – for whatever reason – decided to store the dates and time as integer offsets. Not as a regular DATETIME that you could do date math against.

Luckily, they also provided a function to convert those integer date/time pairs into actual DATETIME values:

msdb.dbo.agent_datetime()

Note that this is an undocumented function. It takes two parameters:

run_date INTEGER

run_time INTEGER

But… that function will fail, with an odd error message, if the values it is processing are zeroes (0). Why? No clue.

And you will occasionally get sysjobhistory entries with zeroes (0) in one or both fields. Then you can’t process those with agent_datetime. Bummer!

In this case you exclude the sysjobhistory entries with zeroes from your query, as you won’t get a proper duration from them.

Example Code

Extra Credit

Review using the undocumented stored procedure master.dbo.xp_sqlagent_enum_jobs in conjunction with agent_datetime.

And…

As ever, if you need help with MEDITECH DR optimization, reporting, extract, index creation or analysis please feel free to give your iatricSystems Account Executive a call!