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 with the least impact on other operations.

The code below inspects the msdb database, displaying 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  |
+---------------------+---------------------+-----------+