SQL Snippet: What SQL Server Agent Jobs were running at that time?

Here is a SQL Snippet that can be used to identify the SQL Server Agent Jobs that were running on a server at a particular point in time.

This can come in very handy if you need to troubleshoot a performance issue after the fact and want to find out if there were any jobs running on your server at a particular point in time retrospectively. The cumbersome alternative is to use a combination of the Job Activity Monitor,Job History and Schedules interfaces within SQL Server Management Studio (SSMS).

To use this snippet simply substitute in the DateTime that you are interested in.

/*--------------------------------------------------------------------------------------------------------------
Date: 29/12/09
Author: John Sansom
Description: Script to identify SQL Server Agent jobs that were
running on the server at a particular time
----------------------------------------------------------------------------------------------------------------*/

DECLARE @jobsRunningAt DATETIME;

SET @jobsRunningAt = '2009/12/28';
WITH JobHistorySummary AS
(
SELECT
jobs.job_id,
job_name = jobs.[name],
step_id,
step_name,
run_time,
run_time_hours = run_time/10000,
run_time_minutes = (run_time%10000)/100,
run_time_seconds = (run_time%10000)%100,
run_time_elapsed_seconds =
(run_time/10000 /*run_time_hours*/ * 60 * 60 /* hours to minutes to seconds*/) +
((run_time%10000)/100 /* run_time_minutes */ * 60 /* minutes to seconds */ ) +
(run_time%10000)%100,
Start_Date = CONVERT(DATETIME, RTRIM(run_date)),
Start_DateTime =
CONVERT(DATETIME, RTRIM(run_date)) +
((run_time/10000 * 3600) + ((run_time%10000)/100*60)
+ (run_time%10000)%100 /*run_time_elapsed_seconds*/)
/ (23.999999*3600 /* seconds in a day*/),
End_DateTime =
CONVERT(DATETIME, RTRIM(run_date))
+ ((run_time/10000 * 3600)
+ ((run_time%10000)/100*60)
+ (run_time%10000)%100)
/ (86399.9964 /* Start Date Time */)
+ ((run_duration/10000 * 3600)
+ ((run_duration%10000)/100*60)
+ (run_duration%10000)%100 /*run_duration_elapsed_seconds*/)
/ (86399.9964 /* seconds in a day*/)
FROM msdb.dbo.sysjobs jobs WITH(NOLOCK)
inner join msdb.dbo.sysjobhistory history WITH(NOLOCK) ON
jobs.job_id = history.job_id
WHERE step_name = '(Job outcome)' --Only interested in final outcome of jobs
)
SELECT
job_id,
job_name,
Start_DateTime,
End_DateTime
FROM JobHistorySummary
WHERE Start_DateTime = @jobsRunningAt
ORDER BY End_DateTime DESC;

Similar Posts

About John Sansom