Joburi ce se vor executa in urmatoarele 24 de ore

Configurare noua (How To)

Situatie

Mai jos este o procedura ce returneaza un raport cu joburile ce urmeaza sa se execute in urmatoarele 24 de ore. In cazul in care ziua curenta este vineri, adica urmeaza weekendul, raportul returneaza joburile ce se vor rula in urmatoarele 72 de ore.
Procedura are 3 parametri:
@MinRuntime – se alegea valoarea minima, in minute, a duratei rularii jobului. Acest parametru verifica in istoricul jobului si nu returneaza joburile ce au un timp mediu de rulare mai mic decat valoarea lui
@Category – se poate alege categoria joburilor care sa fie returnate, daca se lasa % sunt aduse toate
@jobname – se poate alege un singur job pentru care sa se afiseze rularea viitoare, daca se lasa % sunt aduse toate

Solutie

— exec BD_Upcoming_JobSchedules @MinRuntime = 0

CREATE procedure BD_Upcoming_JobSchedules
(@MinRuntime int = 0, @category sysname = ‘%’, @jobname sysname = ‘%’)

as
begin
set nocount on
set datefirst 7

select left(d.name,16) as Category
, left(b.name,50) as Jobname
, left(e.name,50) as Schedule
, substring(‘SunMonTueWedThuFriSat’, datepart(weekday, cast(a.next_run_date as varchar))*3-2,3) as Day
, left(right(‘000000’ + cast(a.next_run_time as varchar),6),2) + ‘:’ +
substring(right(‘000000’ + cast(a.next_run_time as varchar),6),3,2) as Time
, c.run_duration “Average Time”
from msdb.dbo.sysjobschedules a
join msdb.dbo.sysjobs b
on a.job_id = b.job_id
join (select job_id, avg(run_duration) as run_duration from msdb.dbo.sysjobhistory group by job_id) c
on b.job_id = c.job_id
join msdb.dbo.syscategories d
on b.category_id = d.category_id
join msdb.dbo.sysschedules e
on a.schedule_id = e.schedule_id
where a.next_run_date
between convert(varchar,getdate(),112)
and convert(varchar,dateadd(day, case datepart(weekday, getdate()) when 6 then 3 else 1 end,getdate()),112)
and b.name like @jobname
and d.name like @category
and c.run_duration/100 > @MinRuntime
order by next_run_date, next_run_time
end
go

Tip solutie

Permanent
Etichetare:

Voteaza

(13 din 36 persoane apreciaza acest articol)

Despre Autor

Leave A Comment?