Check for same jobs executions that overlapped

same_jobs_overlapping_executions.sql

WITH jobs_runstats AS
(
	SELECT
		J.job_id AS job_id
	,	J.[name] AS job_name
	,	CAST(CAST(H.run_date AS varchar(8)) + ' ' + STUFF(STUFF(RIGHT(REPLICATE('0', 6) +  CAST(H.run_time as varchar(6)), 6), 3, 0, ':'), 6, 0, ':') AS datetime) AS start_execution_date
	,	DATEADD(second, (CAST(LEFT(RIGHT(REPLICATE('0', 6) +  CAST(H.run_duration as varchar(6)), 6), 2) as int) * 3600) + (CAST(SUBSTRING(RIGHT(REPLICATE('0', 6) +  CAST(H.run_duration as varchar(6)), 6), 3, 2) AS int) * 60) + CAST(RIGHT(RIGHT(REPLICATE('0', 6) +  CAST(H.run_duration as varchar(6)), 6), 2) AS int), CAST(CAST(H.run_date AS varchar(8)) + ' ' + STUFF(STUFF(RIGHT(REPLICATE('0', 6) +  CAST(H.run_time as varchar(6)), 6), 3, 0, ':'), 6, 0, ':') AS datetime)) AS stop_execution_date
	,	(CAST(LEFT(RIGHT(REPLICATE('0', 6) +  CAST(H.run_duration as varchar(6)), 6), 2) as int) * 3600) + (CAST(SUBSTRING(RIGHT(REPLICATE('0', 6) +  CAST(H.run_duration as varchar(6)), 6), 3, 2) AS int) * 60) + CAST(RIGHT(RIGHT(REPLICATE('0', 6) +  CAST(H.run_duration as varchar(6)), 6), 2) AS int) AS run_time_secs
	FROM	msdb.dbo.sysjobs AS J
	JOIN	msdb.dbo.sysjobhistory AS H
	  ON	J.job_id = H.job_id
	WHERE	H.step_id = 0 AND H.run_duration >= 0
	  AND	H.run_date > CAST(CONVERT(varchar(8), DATEADD(day, -30, GETDATE()), 112) AS int)
)
SELECT
		J1.job_name
,		J1.start_execution_date
,		J1.stop_execution_date
,		J1.run_time_secs
,		J2.start_execution_date AS overlapping_start_execution_date
,		J2.stop_execution_date AS overlapping_stop_execution_date
,		J2.run_time_secs AS overlapping_run_time_secs
FROM	jobs_runstats AS J1
JOIN	jobs_runstats AS J2
  ON	J1.job_name = J2.job_name
WHERE
	J2.start_execution_date > J1.start_execution_date AND J2.start_execution_date < J1.stop_execution_date
ORDER BY
	J1.job_name, J1.start_execution_date