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.

About John Sansom

I’m a Microsoft Certified Master(MCM) of SQL Server. I’ve been working with database technology in a variety of flavors for over fifteen years. I absolutely love what I do and genuinely feel privileged to be a part of our tremendous technology community. Got a question about SQL Server or being a DBA? Ask me!


Comments

  1. 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

Leave a comment

*