Find Overlapping Jobs

CREATE PROCEDURE OverlappingJobs_sp @HoursBack int = NULL AS

/*
Script lists all overlapping jobs that ran on selected server within last @Hoursback hours.
Basically, idea behind this is to find jobs that are running at the same time, because that might significantly slow down server, 
especially if you have jobs that are running during business hours. 
You can do the same by looking into Jobs History in Enterprise Manager, but this script seems like much easier solution to me.

Parameters 
@Hoursback – Number of hours you want to look back. Default is 24. 

NOTICE: Procedure is based on sysjobshistory table, which stores only 1000 records, at least on my servers. 
If you have very many jobs, and specify long period to look back, procedure might not return all overlapping jobs.

Author: Damir Boticki, 2004

All comments to: bothawpg@yahoo.com

*/

SET NOCOUNT ON

IF @HoursBack IS NULL SET @HoursBack = 24

CREATE TABLE #JOBS (JobID uniqueidentifier, Step int, SDate varchar(8), STime varchar(6), SDuration varchar(6),SecDuration int,
StartTime datetime, EndTime datetime)

INSERT INTO #Jobs (JobID, Step, SDate, STime, SDuration)
SELECT job_id, step_id, CAST(run_date AS varchar(8)),
CASE  
WHEN run_time < 10 THEN '00000' + CAST(run_time AS varchar(1))
WHEN run_time >= 10 AND run_time  < 100 THEN '0000' + CAST(run_time AS varchar(2))
WHEN run_time >= 100 AND run_time < 1000  THEN '000' + CAST(run_time AS varchar(3))
WHEN run_time >= 1000 AND run_time < 10000 THEN '00' + CAST(run_time AS varchar(4))
WHEN run_time >= 10000 AND run_time < 100000 THEN '0' + CAST(run_time AS varchar(5))
WHEN run_time >= 100000 THEN CAST(run_time AS varchar(6))
END, 
CASE  
WHEN run_duration < 10 THEN '00000' + CAST(run_duration AS varchar(1))
WHEN run_duration >= 10 AND run_duration  < 100 THEN '0000' + CAST(run_duration AS varchar(2))
WHEN run_duration >= 100 AND run_duration < 1000  THEN '000' + CAST(run_duration AS varchar(3))
WHEN run_duration >= 1000 AND run_duration < 10000 THEN '00' + CAST(run_duration AS varchar(4))
WHEN run_duration >= 10000 AND run_duration < 100000 THEN '0' + CAST(run_duration AS varchar(5))
WHEN run_duration >= 100000 THEN CAST(run_duration AS varchar(6))
END
FROM msdb..sysjobhistory WHERE Datediff(hh, CONVERT(Datetime,CAST(run_date AS varchar(8)), 108), GetDate()) <= @HoursBack
AND step_id > 0

UPDATE #JOBS SET StartTime = CONVERT (datetime, Left(SDate, 4) + '-' + Substring(SDate, 5, 2) + '-' + Substring(SDate, 7, 2) + ' ' + 
Left(STime, 2) + ':' + Substring(STime, 3, 2) + ':' + Substring(STime, 5, 2), 120)
UPDATE #JOBS SET SecDuration = Cast(Left(SDuration, 2) AS Int) * 60 * 60 + Cast(Substring(SDuration, 3,2) AS Int) * 60 
+ Cast(Substring(SDuration, 5,2) AS int)
UPDATE #JOBS SET EndTime = DateAdd(ss, SecDuration, StartTime)

SELECT T3.Name AS FirstJobName, T4.Name As SecondJobName,
CASE 
WHEN T1.EndTime < T2.EndTime THEN DateDiff(ss,T2.StartTime, T1.EndTime)
ELSE DateDiff(ss,T2.StartTime, T2.EndTime) END AS OverlapTimeInSec,
T1.Step AS FirstJobStep, T1.StartTime AS FirstJobStart, T1.EndTime AS FirstJobEnd, 
T2.Step AS SecondJobStep, T2.StartTime AS SecondJobStart, T2.EndTime AS SecondJobEnd FROM 
#Jobs t1 JOIN #Jobs t2 ON T1.StartTime < T2.StartTime AND T1.EndTime > T2.StartTime
JOIN msdb..sysjobs T3 ON T1.JobID = T3.Job_ID
JOIN msdb..sysjobs T4 ON T2.JobID = T4.Job_ID

DROP TABLE #JOBS
SET NOCOUNT OFF
GO