Useful Query #1 - find overlapping SQL Agent jobs

 

In SQL 2008 (or using 3rd party tools) you can run this command against multiple servers to determine which SQL Agent jobs overlap.  Perfect for finding contentious jobs...

use msdb
go

with cte  (name, server, run_date,run_time,run_duration,enddate,startdatetime,enddatetime,retries_attempted) as (
select --j.name, h.step_id, h.step_name, h.sql_message_id, h.sql_severity, h.message, run_status,
j.name,server, run_date, run_time, run_duration, run_time + run_duration enddate,

dateadd(hh, run_time / 10000,
            dateadd(mi, (run_time % 10000)/100,
            dateadd(ss, run_time %100,
            cast(cast(run_date as char(8)) as datetime)))) startdatetime,
            dateadd(ss,run_duration,dateadd(hh, run_time / 10000,
            dateadd(mi, (run_time % 10000)/100,
            dateadd(ss, run_time %100,
            cast(cast(run_date as char(8)) as datetime))))) enddatetime,

retries_attempted
from sysjobs j
inner join sysjobhistory h on h.job_id = j.job_id and h.step_id = 1
where run_date > convert(varchar(10),dateadd(d,-1,getdate()),112)
--and run_status <> 1
)
select cte.server, cte.name, cte.startdatetime, cte1.enddatetime, cte.run_duration, cte1.server, cte1.name, cte1.startdatetime, cte1.enddatetime, cte1.run_duration
from cte
cross join cte cte1
where (cte.name <> cte1.name)

and (cte.startdatetime between cte1.startdatetime and cte1.enddatetime
    or cte.enddatetime between cte1.startdatetime and cte1.enddatetime)
    and cte.run_duration > 300