SQL script to find overLapping SQL Jobs

On your SQL server, you will see a lot of jobs. For backups, for SQL maintenance or jobs specific to your application. If these jobs overlap and during the execution they are trying to read/modify the same database, they might go in a deadlock.

To find out the jobs which are overlapping, here is a stored procedure which can help you find the SQL jobs which are overlapping.

I would like to thank Viorel Iftode who helped me to write this script. Check out his blogs at www.vioreliftode.com

/* Name: CheckOverlappingJobs
Author: Shubham Bansal

Version: 1.0

Date: 11-Jul-2017

Desciption: This stored procedure checks all the SQL Jobs and finds out if their execution is overlapping with each other.

Parameters: 1. Number of days of Job history to be exmamined. Default is 90 Days 

2. Job names which needs to be excluded. Provide the names of jobs as a string seperated by any delimiter. Default is blank.

Create Procedure [dbo].[CheckOverlappingJobs]

@DaysOfJobHistory int = 90,

@JobNamesToExclude nvarchar(max) = ''

AS-- Check if the temporary tables #SQLJobs and #Overlapping Jobs exists and if yes, delete the tables

If Not( (OBJECT_ID('tempdb..#SQLJobs') is null) )

begin 

    Drop Table #SQLJobs 

end

If Not(OBJECT_ID('tempdb..#OverLappingJobs') is null)

Begin

  Drop table #OverLappingJobs

End
-- Declaring variables to be used by cursor

Declare @CurrentJobName nvarchar(255)

Declare @CurrentJobStartTime datetime

Declare @CurrentJobEndTime datetime
-- Creating temporary tables #SQLJobs and #OverlappingJobs

Create Table #SQLJobs(JobName nvarchar(255),JobID nvarchar(255),StartTime datetime,EndTime datetime, Duration time,JobStatus nvarchar(255))

Create Table #OverLappingJobs(JobID nvarchar(255), JobName nvarchar(255), JobStartTime datetime,  EndDateTime datetime, OverlappingJobName nvarchar(255), OverLappingJobStartTime datetime,  OverLappingJobEndTime datetime,  )

Insert into #SQLJobsselect JobName, JobID, StartTime, (StartTime + CAST(Duration as datetime)) as EndTime, Duration, JobStatus from (Select J.job_id as JobID, J.name as JobName, (CAST(CAST (RTRIM(run_date) as date) as datetime)) +(CAST(CAST (DATEADD(Second, JH.run_time%100 + 60*((JH.run_time%10000)/100) + 3600*(JH.run_time/10000),0) As time) as datetime)) as StartTime,CAST(DATEADD(Second, JH.run_Duration%100 + 60*((JH.run_Duration%10000)/100) + 3600*(JH.run_Duration/10000),0) As time) as Duration,CASE When JH.run_status =0 Then 'Failed' When Jh.run_status= 1 Then 'Success' When JH.run_status= 2 Then 'Retry' When JH.run_status= 3 Then 'Cancelled' else 'Unknown' End [JobStatus]from msdb.dbo.sysjobhistory JH, msdb.dbo.sysjobs J where JH.job_id = J.job_id and JH.step_id = 0 ) subquery where StartTime >= (GetDate() - @DaysOfJobHistory) and Duration <> '00:00:00:000' and @JobNamesToExclude Not like '%'+ JobName +'%'

-- Declaring cursor for the select query
Declare JobCursor cursor for
  select JobName, StartTime, EndTime from #SQLJobs
 -- Open the cursor to access records from the select query 
Open JobCursor
 -- Moving cursor to the next row of the result 
Fetch NEXT from JobCursor Into @CurrentJobName, @CurrentJobStartTime, @CurrentJobEndTime 
while (@@FETCH_STATUS=0) 
begin
 -- Finding the Overlapping jobs 
Insert into #OverLappingJobs select JobID, @CurrentJobName, @CurrentJobStartTime, @CurrentJobEndTime, Jobname as OverlappingJobName, StartTime as OverLappingJobStartTime, EndTime as OverlappingJobEndTime  from #SQlJobs
 where @CurrentJobName <> #SQlJobs.JobName and  ((@CurrentJobStartTime >= StartTime and @CurrentJobStartTime <= EndTime) or ((@CurrentJobEndTime>= StartTime) and (@CurrentJobEndTime <= StartTime))) 
Fetch NEXT from JobCursor Into @CurrentJobName, @CurrentJobStartTime, @CurrentJobEndTime 
End
Close JobCursor
-- Final result of overlapping Jobs
Select * from #OverLappingJobs order by JobStartTime Desc
-- Cleanup of Cursor and Temporary tables
Deallocate JobCursor
Drop table #SQlJobs
Drop table #OverLappingJobs

 2,005 total views,  1 views today