Finding When A SQL Agent Job Ran

A quick post for today since I haven't added anything in quite a while.

Recently, I was investigating a deadlock that was occurring, like clockwork, on a particular server. A trace revealed that it was being executed by the SQL Server Agent account. The host machine that executed a query involved in the deadlock is our SSIS host. Therefore, there are loads of jobs with a dizzying array of schedules to weed through.

So I needed a query that can tell me what job(s) were running at a particular point in time. The key table is the msdb.dbo.sysjobhistory table, which, as it says on the tin, keeps a record of job executions. There is one main hurdle when it comes to querying this table: the run_date, run_time and run_duration columns are all stored as INT (https://msdn.microsoft.com/en-gb/library/ms174997.aspx). The run_duration is the most difficult to deal with since it is a variable length value: i.e. a 5 second duration is stored as 5 and a 2 hour duration is stored as 20000!

Helpfully, Microsoft provides a function - msdb.dbo.agent_datetime(run_date, run_time) - to calculate the run_time of a job (curiously, I can't find any official MSDN documentation on this function). So converting the two columns run_date and run_time is easy. More difficult is formatting the run_duration into a time value.

There is an additional problem involved. The sysjobhistory table doesn't record the date and time that jobs finish. This needs to be calculated by taking the start time (run_date and run_time) and adding the run_duration to it.

I've created the query below as follows:

• @RunTime parameter is the point in time when you need to find running jobs
• The CTE converts the run_duration INT value to a VARCHAR
• It filters on step_id 0 (this is job outcome which shows the total job duration and success or failure of the job)
• Finally I search for jobs starting <= @RunTime and ending <= @RunTime 

There is an additional dependency, the SQL Agent job history settings. generally, i would use the above query for very recent job executions. It is unlikely that you'd be able to find what job was running 6 weeks ago (depending on how busy your SQL Agent is, of course).

I hope you find this helpful.

Thanks to:

• SQL And Me for drawing my attention to the agent_datetime function.
• SwePeso for the DATEADD query suggested on SQLServerCentral
• SQLDenis on LessThanDot for the CTE to and time formatting of run_duration