Finding SQL Server Agent Jobs Running at a Particular Time

I’m guessing I’m not the first person to come up with the need to try and correlate a problem or issue on a server against whether or not a SQL Server Agent job was running or not. Only when I recently did a Google search to see if there were any scripts out there that could let me see what, if any, jobs (and job steps) were running a particular time, I came up empty-handed.

Related: DBAs and the SQL Server Agent

A Million Reasons Why

There are probably a million reasons why you could want to try and track down what job(s) might have been running at a particular time. Most of them, of course, will be due to various performance issues, side effects, and other problems that you might end up observing on one of your servers at a given time—and having the ability to quickly rule out a SQL Server Agent Job (or know that it was the culprit) is therefore, a godsend.

SQL Agent Jobs screenshot

To illustrate, consider the above screenshot—where I was able to spot a disk spike of roughly 370MB/sec on a particular drive and wanted to know if that was either a query from hell, some sort of job, or (my hunch—given the hour and the I/O involved) some sort of index maintenance job.   

Dealing with Data Structures from the Early 1990s

Overall, building a script to go through and query whether or not a given job was running at a particular time is (or would be), all but trivial. SQL Server Agent job execution details are all stored in the msdb database, and run information per each job/job step execution are all stored in more or less straightforward manner. The only issue, of course, is that older parts of SQL Server (like SQL Server Agent Job Execution details), were written back in the day when a mindset of conserving even a single, measly, byte was still the norm. As such, the date and time execution columns in msdb.dbo.sysjobhistory still (and, might I add, stupidly) try to squeeze out a few extra nibbles by expressing dates and times as integers. (Actually, I always jokingly and half-heartedly assume that’s what was going on— because 2 integers ends up taking up 8 bytes—the same as a single datetime—so, truth is, I have no idea what the folks who created these columns were smoking or what constraints they were working against. I just know that it’s probably time for Microsoft to consider revisiting these columns—hence my gruff tone.)

The short version, though, is that if you’d like to specify a specific date + time, such as 1:47:59AM on 2014-03-22 (as per the screenshot above), then you’ll need to massage integer dates + times from sysjobhistory into something a bit more manageable and queryable like, well, datetimes.


But enough commentary—on to the actual script:

  1. DECLARE @targetTime datetime = '2014-05-04 06:04:49';
  2. -- SET @targetTime = 'xxx' -- for SQL Server 2008 and less
  4. -- convert to string, then int:
  5. DECLARE @filter int = CAST(CONVERT(char(8), @targetTime, 112) AS int);
  7. WITH times AS (
  9. job_id,
  10. step_name,
  11. LEFT(run_date, 4) + '-' + SUBSTRING(CAST(run_date AS char(8)),5,2)
  12.         + '-' + RIGHT(run_date,2) + ' ' +
  13.         LEFT(REPLICATE('0', 6 - LEN(run_time))
  14.         + CAST(run_time AS varchar(6)), 2) + ':' +
  15.         SUBSTRING(REPLICATE('0', 6 - LEN(run_time))
  16.         + CAST(run_time AS varchar(6)), 3, 2) + ':'
  17.         + RIGHT(REPLICATE('0', 6 - LEN(run_time))
  18.         + CAST(run_time AS varchar(6)), 2) AS [start_time],
  19. '2010-01-01 ' + LEFT(REPLICATE('0', 6 - LEN(run_duration))
  20.         + CAST(run_duration AS varchar(6)), 2) + ':' +
  21.         SUBSTRING(REPLICATE('0', 6 - LEN(run_duration))
  22.         + CAST(run_duration AS varchar(6)), 3, 2) + ':' +
  23.         RIGHT(REPLICATE('0', 6 - LEN(run_duration))
  24.         + CAST(run_duration AS varchar(6)), 2) [duration]
  25. FROM
  26.         msdb.dbo.sysjobhistory
  27. WHERE
  28.         run_date IN (@filter - 1, @filter, @filter + 1)
  29. )
  31. SELECT
  33.         t.step_name,
  34.         t.start_time,
  35.         DATEADD(ss, DATEDIFF(ss, '2010-01-01 00:00:00',
  36.                 duration), start_time) [end_time]
  37. FROM
  38.         times t
  39.         INNER JOIN msdb.dbo.sysjobs j ON j.job_id = t.job_id
  40. WHERE
  41.         @targetTime >= start_time
  42.                 AND @targetTime <= DATEADD(ss, DATEDIFF(ss, '2010-01-01 00:00:00',
  43.                         duration), start_time)
  44. ORDER BY
  45.         start_time ASC;

To use this script—or find which SQL Agent Jobs may have been running at a specified time—just change the value of the @targetTime and execute. (The query is a bit rough and will cause a scan of your clustered index—so if you’ve got bazillions of jobs, you might want to observe costs before executing willy-nilly.) If there are any matches, they’ll be displayed—otherwise nothing was executing during the time specified.

Related: Truncating SQL Server Job History

Discuss this Blog Entry 4

on May 8, 2014

Nice concept, this will be useful no doubt.

Out of interest there is a system function that you can use to simplify the conversion of the job history date and time values to DATETIME, here's a revised version of the SELECT in the CTE:

SELECT job_id,
msdb.dbo.agent_datetime(run_date, run_time) AS start_time,
msdb.dbo.agent_datetime(20100101, run_duration) AS duration
run_date IN (@filter - 1, @filter, @filter + 1)
AND step_id <> 0

on May 28, 2014

Will this work for SQL Server 2012?

on Oct 20, 2014

@smichel Yes - this will work just fine under SQL Server 2012 (or at least it does on my servers).

on Jan 12, 2015

I had some issues when runduration was exceeding 24 hours i.e. 1 day. I would get an error while converting varchar to datetime.

Therefore I made a slight modification to handle jobs running greater than one day:

DECLARE @targetTime datetime = '2015-01-07 01:31:49';
-- SET @targetTime = 'xxx' -- for SQL Server 2008 and less

-- convert to string, then int:
DECLARE @filter int = CAST(CONVERT(char(8), @targetTime, 112) AS int);

WITH times AS (
LEFT(run_date, 4) + '-' + SUBSTRING(CAST(run_date AS char(8)),5,2)
+ '-' + RIGHT(run_date,2) + ' ' +
LEFT(REPLICATE('0', 6 - LEN(run_time))
+ CAST(run_time AS varchar(6)), 2) + ':' +
SUBSTRING(REPLICATE('0', 6 - LEN(run_time))
+ CAST(run_time AS varchar(6)), 3, 2) + ':'
+ RIGHT(REPLICATE('0', 6 - LEN(run_time))
+ CAST(run_time AS varchar(6)), 2) AS [start_time],
((run_duration/10000*3600 + (run_duration/100)%100*60 + run_duration%100 + 31 ) / 60)
as 'RunDurationMins'
run_date IN (@filter - 1, @filter, @filter + 1)

DATEADD(MINUTE, RunDurationMins,start_time) [end_time]
times t
INNER JOIN msdb.dbo.sysjobs j ON j.job_id = t.job_id
@targetTime >= start_time
AND @targetTime <= DATEADD(MINUTE, RunDurationMins,start_time)
start_time ASC;

Works well for me so far.

Please or Register to post comments.

What's Practical SQL Server?

Practical advice, insight, and help for core SQL Server considerations.


Michael K. Campbell

Michael K. Campbell is a contributing editor for SQL Server Pro and Dev Pro and is an ASPInsider. Michael is the president of OverAchiever Productions, a consultancy dedicated to technical evangelism...
Blog Archive

Sponsored Introduction Continue on to (or wait seconds) ×