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.

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:

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  |
+---------------------+---------------------+-----------+
Ads by Google, Paying the Rent: