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

• Identify All Active SQL Server Sessions
• How to identify the most costly SQL Server queries using DMV’s
• Highest SQL Server Waits by Percentage

I hope you find this SQL Snippet useful in your administration of SQL Server. If you have any questions regarding this snippet, SQL Server Agent Jobs or anything whatsoever to do with SQL Server then feel free to ask.