Situatie
Mai jos este o procedura care pentru un server sql, returneaza pentru ultima saptamana joburile care s-au suprapus in timpul executiei. Pentru eficienta procedura ia in calcul doar joburile care au timpul de executie mai mare de 5 minute.
Solutie
create procedure SuprapunereJoburi
as
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
)
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)
Leave A Comment?