Gaps between SQL Server Agent Jobs

For a busy server, with lots of SQL Server Agent Jobs, it can be difficult to determine the optimal time to perform maintenance. I’ve written a small piece of code that inspects the msdb database to determine gaps between SQL Server agent jobs.

/*
      Shows gaps between agent jobs
*/
DECLARE @EarliestStartDate DATETIME;
DECLARE @LatestStopDate DATETIME;
SET @EarliestStartDate = DATEADD(DAY, -1, GETDATE());
SET @LatestStopDate = GETDATE();

;WITH s AS 
(
	SELECT StartDateTime = msdb.dbo.agent_datetime(sjh.run_date, sjh.run_time)
		  , MaxDuration = MAX(sjh.run_duration)
	FROM msdb.dbo.sysjobs sj 
		  INNER JOIN msdb.dbo.sysjobhistory sjh ON sj.job_id = sjh.job_id
	WHERE sjh.step_id = 0
		AND msdb.dbo.agent_datetime(sjh.run_date, sjh.run_time) >= @EarliestStartDate
		AND msdb.dbo.agent_datetime(sjh.run_date, sjh.run_time) < = @LatestStopDate
	GROUP BY msdb.dbo.agent_datetime(sjh.run_date, sjh.run_time)
	UNION ALL
	SELECT StartDate = DATEADD(SECOND, -1, @EarliestStartDate)
		, MaxDuration = 1
	UNION ALL 
	SELECT StartDate = @LatestStopDate
		, MaxDuration = 1
)
, s1 AS 
(
SELECT s.StartDateTime
	, EndDateTime = DATEADD(SECOND, s.MaxDuration - ((s.MaxDuration / 100) * 100)
		+ (((s.MaxDuration - ((s.MaxDuration / 10000) * 10000)) 
                    - (s.MaxDuration - ((s.MaxDuration / 100) * 100))) / 100) * 60
		+ (((s.MaxDuration - ((s.MaxDuration / 1000000) * 1000000)) 
                    - (s.MaxDuration - ((s.MaxDuration / 10000) * 10000))) / 10000) * 3600, s.StartDateTime)
FROM s
)
, s2 AS
(
	SELECT s1.StartDateTime
		, s1.EndDateTime
		, LastEndDateTime = LAG(s1.EndDateTime) OVER (ORDER BY s1.StartDateTime)
	FROM s1 
)
SELECT GapStart = CONVERT(DATETIME2(0), s2.LastEndDateTime)
	, GapEnd = CONVERT(DATETIME2(0), s2.StartDateTime)
	, GapLength = CONVERT(TIME(0), DATEADD(SECOND, DATEDIFF(SECOND, s2.LastEndDateTime, s2.StartDateTime), 0))
FROM s2 
WHERE s2.StartDateTime > s2.LastEndDateTime
	ORDER BY s2.StartDateTime;

The code above requires SQL Server 2012+ since it uses the LAG aggregate.

On SQL Server 2005, SQL Server 2008, and SQL Server 2008 R2, you could replace the LastEndDateTime column definition with:

LastEndDateTime = (SELECT TOP(1) s1a.EndDateTime FROM s1 s1a WHERE s1a.rn = s1.rn - 1)

The output from this code looks like:

+---------------------+---------------------+-----------+
| GapStart            | GapEnd              | GapLength |
+---------------------+---------------------+-----------+
|                     |                     |           |
| 2016-05-30 21:00:00 | 2016-05-30 21:15:00 | 00:15:00  |
|                     |                     |           |
| 2016-05-30 21:18:41 | 2016-05-30 21:40:00 | 00:21:19  |
|                     |                     |           |
| 2016-05-30 21:40:24 | 2016-05-30 21:42:00 | 00:01:36  |
|                     |                     |           |
| 2016-05-30 21:42:00 | 2016-05-30 21:45:00 | 00:03:00  |
|                     |                     |           |
| 2016-05-30 21:45:03 | 2016-05-30 22:42:00 | 00:56:57  |
|                     |                     |           |
| 2016-05-30 22:42:02 | 2016-05-30 23:42:00 | 00:59:58  |
|                     |                     |           |
| 2016-05-30 23:42:00 | 2016-05-31 01:15:00 | 01:33:00  |
|                     |                     |           |
| 2016-05-31 01:55:49 | 2016-05-31 02:00:00 | 00:04:11  |
|                     |                     |           |
| 2016-05-31 02:00:02 | 2016-05-31 06:15:00 | 04:14:58  |
|                     |                     |           |
| 2016-05-31 06:15:01 | 2016-05-31 06:42:00 | 00:26:59  |
|                     |                     |           |
| 2016-05-31 06:42:01 | 2016-05-31 07:00:00 | 00:17:59  |
+---------------------+---------------------+-----------+