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

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.

  • Pingback: SQL Snippet: Who owns that SQL Server Job? | Santech Solutions()

  • Pingback: SQL Snippet: Who owns that SQL Server Job? | John Sansom - SQL Server DBA in the UK()

  • Pingback: SQL Snippet: Who owns that SQL Server Job?()

  • https://www.facebook.com/andreas.jansson.5817 Andreas Jansson

    Very useful script! I changed the WHERE clause though, since I wanted jobs that had begun earlier as well, and not only the ones started at the exact requested point in time.

    WHERE @jobsRunningAt BETWEEN Start_DateTime AND End_DateTime

    • http://www.johnsansom.com John Sansom

      Andreas – Glad you found the script useful.