Report overlapping jobs

,

This script reports jobs which are running when another job is also running. This could be a reason for performance degradations.

Copy and past this script and run it as a query. Study the outcome to make decisions for shifting the timeframe for a job.

/*
	shows jobs with overlapping runtime
	Wilfred van Dijk - wvand@wilfredvandijk.nl
		
	- minimum runtime: 5 minutes
	- only scheduled jobs are checked
	- only jobs from the last 7 days
*/
;with CTE as (
	SELECT b.name as job_name, a.start_execution_date, a.stop_execution_date, datediff(minute, a.start_execution_date, a.stop_execution_date) as run_time
	FROM msdb.dbo.sysjobactivity a
	join msdb.dbo.sysjobs b
	on a.job_id = b.job_id
	WHERE a.start_execution_date > DATEADD(dd, -7, GETDATE()) -- date criteria
	and datediff(minute, a.start_execution_date, a.stop_execution_date) > 5 -- runtime criteria
	and a.run_requested_source = 1 -- scheduler only
)
select a.job_name, a.start_execution_date, a.run_time, b.job_name, b.start_execution_date, b.run_time
from CTE a, CTE b
where a.start_execution_date between b.start_execution_date and b.stop_execution_date
and a.job_name <> b.job_name
and (a.start_execution_date > b.start_execution_date or a.stop_execution_date between b.start_execution_date and b.stop_execution_date)
;

Rate

4.5 (8)

Share

Share

Rate

4.5 (8)