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.

Script

But enough commentary—on to the actual script:

DECLARE @targetTime datetime = '2014-05-04 06:04: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 (
SELECT 
job_id,
step_name,
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],
'2010-01-01 ' + LEFT(REPLICATE('0', 6 - LEN(run_duration)) 
	+ CAST(run_duration AS varchar(6)), 2) + ':' + 
	SUBSTRING(REPLICATE('0', 6 - LEN(run_duration)) 
	+ CAST(run_duration AS varchar(6)), 3, 2) + ':' + 
	RIGHT(REPLICATE('0', 6 - LEN(run_duration)) 
	+ CAST(run_duration AS varchar(6)), 2) [duration]
FROM 
	msdb.dbo.sysjobhistory
WHERE
	run_date IN (@filter - 1, @filter, @filter + 1)
)

SELECT 
	j.name,
	t.step_name,
	t.start_time, 
	DATEADD(ss, DATEDIFF(ss, '2010-01-01 00:00:00', 
		duration), start_time) [end_time]
FROM 
	times t
	INNER JOIN msdb.dbo.sysjobs j ON j.job_id = t.job_id
WHERE
	@targetTime >= start_time
		AND @targetTime <= DATEADD(ss, DATEDIFF(ss, '2010-01-01 00:00:00', 
			duration), start_time)
ORDER BY 
	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.

Comments

Plain text