Gaps between SQL Server Agent Jobs

2016-06-10 · by Hannah Vernon · in SQL Server Agent, tools

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;

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)

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

Tags: jobs, maintenance, sql-server-agent

Gaps between SQL Server Agent Jobs

Related

One Response

Leave a ReplyCancel reply

Search

Categories

Pages

Meta