As once a data architect, I cannot help but admiring and appreciating the data model behind the sql server job schedules. The core design is actually just one table, msdb.dbo.sysschedules, but the design beauty/elegance of the table is that it can support many different types of schedule with a rich set of variations for each, for example, a schedule can be: on the weekdays of 2nd week of every 3 months, run every 30 min between 1:00am to 3:00pm starting at 1:15am.
Recently, I have been working on a task:
With two parameters, @Start_DateTime and @End_DateTime, find the all/specific scheduled jobs that will run during this time window, and list the exact scheduled run time for each job.
I initially try to google out a solution, but I simply cannot find out one that works to my needs, the closest one I can google out is this “Keep Track of all your jobs schedules”, but it has some deficiencies that cannot generate correct result, for example, if the job schedule is daily running every hour from 8:00am to 8:00pm, and you want to find out the job schedules between 10:05am to 1:05pm, the result will give you all schedules from 8:00am to 8:00pm instead of just between 10:05am and 01:05pm, and sometimes, it simply did not give out any job schedules.
So I start to work on my own, I have to say it is a fun / rewarding journey, my 1st version is about 1,300 lines with complicated if/else/case when, and my 2nd version is about 800 lines and my 3rd (the current) version is about 400 lines and I have tested in various weird schedules (in sql server 2012 and a few sql server 2008 cases as well), like a schedule of the 2nd Tuesday/Friday of every 3 months, and also a job with multiple daily / weekly schedules etc, and I always get the correct results.
I will give a few explanations regarding the code, so it is better to understand.
1. The @Start_DateTime and @End_DateTime should be in future, i.e. they should > getdate().
2. I need to rely on next_run_date/next_run_time of msdb.dbo.sysjobschedules, which will be refreshed every 20 min as indicated on BOL. This means if you create a new job with a new schedule, the next_run_date / next_run_time columns will not be populated immediately and if so, my script may not return correct results.
3. The most difficult part is about the calculation of “the <nth> <weekday> of every <X> months”, the difficulty here is that weekday as in datepart(weekday, <@date>) is impacted by the @@DateFirst setting (language dependent). However, I was lucky to find a language-independent solution in Itzik Ben-Gan’s series articles (DateTime Calculations).
if object_id('dbo.uspCheckJobFutureSchedule', 'P') is not null
drop proc dbo.uspCheckJobFutureSchedule;
gocreate proc dbo.uspCheckJobFutureSchedule
@Start_DT datetime
, @End_DT datetime
, @JobName varchar(128)=''
, @ScheduleName varchar(128)=''
asbegin --proc
if object_id('tempdb.dbo.#tmp', 'U') is not null
drop table #tmp;
if object_id('tempdb.dbo.#job', 'U') is not null
drop table dbo.#job;
set nocount on;
if object_id('tempdb..#tblCalendar', 'U') is not null
drop table #tblCalendar;
create table #tblCalendar (dt smalldatetime, [year] smallint, [month] tinyint, [day] tinyint, [weekday] tinyint, [week_of_month] tinyint)
declare @tbl table (dt smalldatetime, [year] smallint, [month] tinyint, [day] tinyint, [weekday] tinyint, [week_of_month] tinyint);
--declare @start_dt datetime, @end_dt datetime;declare @diff_days int, @calc_start_dt datetime, @i int, @inner_loop_dt datetime;
declare @current_dt datetime, @current_week datetime;declare @dw int;
declare @calc_end_dt datetime; declare @min_end_date datetime, @max_start_date datetime, @dt datetime;declare @next_run_date int, @next_run_time int, @next_run_dt datetime;
--select @start_dt = '2014-02-21', @end_dt = '2014-04-28';
-- first get all jobs that are scheduled, with job name and the schedule details create table #tmp (
schedule_id int, jobname sysname
,schedule_name sysname
,freq_type int ,freq_interval int ,freq_subday_type int ,freq_subday_interval int ,freq_relative_interval int ,freq_recurrence_factor int ,active_start_date char(8) ,active_end_date char(8) ,active_start_time char(8) ,active_end_time char(8) ,active_start_date_int int ,active_end_date_int int ,active_start_time_int int ,active_end_time_int int ,next_run_date int ,next_run_time int)
insert into #tmp (schedule_id, jobname, schedule_name, freq_type,freq_interval
,freq_subday_type
,freq_subday_interval
,freq_relative_interval
,freq_recurrence_factor
,active_start_date
,active_end_date
,active_start_time
,active_end_time
,active_start_date_int
,active_end_date_int
,active_start_time_int
,active_end_time_int
,next_run_date
,next_run_time
)
select s.schedule_id, j.name, s.name, s.freq_type,s.freq_interval
,s.freq_subday_type
,s.freq_subday_interval
,s.freq_relative_interval
,s.freq_recurrence_factor
,cast(s.active_start_date as char(8))
,cast(s.active_end_date as char(8))
,substring(right('00000' + cast(s.active_start_time as varchar(6)), 6),1,2)+':'+ substring(right('00000' + cast(s.active_start_time as varchar(6)), 6),3,2)+':'+substring(right('00000' + cast(s.active_start_time as varchar(6)), 6),5,2)
,substring(right('00000' + cast(s.active_end_time as varchar(6)), 6),1,2)+':'+ substring(right('00000' + cast(s.active_end_time as varchar(6)), 6),3,2)+':'+substring(right('00000' + cast(s.active_end_time as varchar(6)), 6),5,2)
, s.active_start_date
, s.active_end_date
, s.active_start_time
, s.active_end_time
, js.next_run_date
, js.next_run_time
from msdb.dbo.sysschedules sinner join msdb.dbo.sysjobschedules js
on js.schedule_id = s.schedule_idinner join msdb.dbo.sysjobs j
on j.job_id = js.job_id where s.enabled=1 and j.enabled =1 and js.next_run_date > 0and s.name = case @ScheduleName when '' then s.name else @ScheduleName end
and j.name= case @JobName when '' then j.name else @JobName end
-- delete all job schedules that are beyond the boundary of the @start_dt and @end_dt -- for example, if a job is scheduled to be run in Jan, 2020, and if @start_dt='2018-01-01' and @end_dt = '2018-02-01', -- then we do not need to consider this job schedule (i.e. Jan 2020)delete from #tmp
where convert(datetime, active_start_date +' '+ active_start_time ) > @end_dt
or convert(datetime, active_end_date +' '+ active_end_time ) < @start_dt;
-- create a temp table to hold those jobs that will be started between @start_dt and @end_dt windowcreate table dbo.#job (
ServerName sysname default @@servername
, jobname sysname
, ScheduleName sysname
, run_datetime datetime
);
declare @schedule_id int, @schedule_name sysname, @active_start_dt datetime, @active_end_dt datetime;
declare @freq_type int, @freq_interval int
, @freq_subday_type int , @freq_subday_interval int , @freq_relative_interval int , @freq_recurrence_factor int , @active_start_time_int int , @active_end_time_int int;declare curSch cursor for
select distinct schedule_id, schedule_name, active_start_dt=convert(datetime, active_start_date +' '+ active_start_time )
, active_end_dt=convert(datetime, active_end_date +' '+ active_end_time )
, freq_type, freq_interval
, freq_subday_type
, freq_subday_interval
, freq_relative_interval
, freq_recurrence_factor
, active_start_time_int
, active_end_time_int
, next_run_date
, next_run_time
from #tmp; open curSch;fetch next from curSch into @schedule_id, @schedule_name, @active_start_dt, @active_end_dt
, @freq_type, @freq_interval
, @freq_subday_type
, @freq_subday_interval
, @freq_relative_interval
, @freq_recurrence_factor
, @active_start_time_int
, @active_end_time_int
, @next_run_date
, @next_run_time;
while @@fetch_status = 0
begin -- loop
if @freq_type = 1 -- one time only
begin -- one time only
if @active_start_dt between @start_dt and @end_dt
insert into #job (jobname, ScheduleName, run_datetime)select jobname, @schedule_name, convert(datetime, active_start_date +' '+ active_start_time)
from #tmp where schedule_id = @schedule_idend -- one time only
elsebegin -- else not one time only
select @next_run_dt = convert(datetime, cast(@next_run_date as varchar(10)) +' ' + substring(right('00000' + cast(@next_run_time as varchar(6)), 6),1,2)+':'+ substring(right('00000' + cast(@next_run_time as varchar(6)), 6),3,2)+':'+substring(right('00000' + cast(@next_run_time as varchar(6)), 6),5,2))
select @max_start_date = case when @next_run_dt >= @start_dt then @next_run_dt else @start_dt end,
@min_end_date = case when @active_end_dt >= @end_dt then @end_dt else @active_end_dt end;
select @calc_start_dt = dateadd(month, datediff(month, 0, @max_start_date), 0); -- switch to the month's first day for @max_start_date
select @calc_end_dt = dateadd(month, datediff(month, 0, @min_end_date)+1, 0)-1; -- witch to the month's last day for @min_end_date
-- generate a calendar table btw @calc_start_dt and @calc_end_dt set @inner_loop_dt = @calc_start_dttruncate table #tblCalendar;
begin tran
while @inner_loop_dt <= @calc_end_dt begininsert into #tblCalendar (dt, [year], [month], [day], [weekday], [week_of_month])
select @inner_loop_dt, year(@inner_loop_dt), month(@inner_loop_dt), day(@inner_loop_dt), datepart(dw, @inner_loop_dt + @@datefirst -1)
, datepart(week, @inner_loop_dt) - datepart(week, dateadd(month, datediff(month, 0, @inner_loop_dt), 0)) + 1 ;
set @inner_loop_dt = @inner_loop_dt + 1; endcommit tran
delete from @tbl;
if @freq_type = 4 -- daily
begin -- daily
insert into @tbl (dt)select dt from #tblCalendar
where datediff(day, @max_start_date, dt) >=0 and datediff(day, dt, @min_end_date)>=0
and datediff(day, @active_start_dt, dt)%@freq_interval = 0;
end -- daily
if @freq_type = 8 -- weekly
begin -- weekly
insert into @tbl (dt)select dt from #tblCalendar
where datediff(day, @max_start_date, dt) >=0 and datediff(day, dt, @min_end_date)>=0
and datediff(week, @next_run_dt, dt)%@freq_recurrence_factor = 0 and power(2, datepart(dw, dt+@@datefirst-1)%7) & @freq_interval >=1;end -- weekly
if @freq_type = 16begin -- monthly, @freq_type = 16
insert into @tbl (dt)select dt from #tblCalendar
where datediff(day, @max_start_date, dt) >=0 and datediff(day, dt, @min_end_date)>=0
and datediff(month, @active_start_dt, dt)%@freq_recurrence_factor = 0
and [day]=@freq_interval;
end -- monthly, @freq_type = 16
if @freq_type = 32begin -- monthly, @freq_type = 32
-- find the days that the scheduled jobs will be run (later, we will loop through time to figure out the exact date/time)if @freq_relative_interval in (1, 2, 4, 8)
begin if @freq_interval < 8 begin; with c as (
select dt, [year], [month], [weekday], [day], [week_of_month], rn=row_number() over (partition by year, month order by dt asc)
from #tblCalendarwhere dt between @calc_start_dt and @calc_end_dt
and [weekday] = case @freq_interval when 1 then 7 else @freq_interval -1 end
)
insert into @tbl (dt)select dt from c
where datediff(day, @max_start_date, dt) >=0 and datediff(day, dt, @min_end_date)>=0
and rn = log(@freq_relative_interval)/log(2)+1and datediff(month, @calc_start_dt, dt)%@freq_recurrence_factor = 0;
end if @freq_interval = 8 insert into @tblselect * from #tblCalendar
where datediff(day, @max_start_date, dt) >=0 and datediff(day, dt, @min_end_date)>=0
and [day] = @freq_relative_interval
and datediff(month, @calc_start_dt, dt)%@freq_recurrence_factor = 0;
if @freq_interval=9 -- weekday
insert into @tblselect * from #tblCalendar
where datediff(day, @max_start_date, dt) >=0 and datediff(day, dt, @min_end_date)>=0
and week_of_month = log(@freq_relative_interval)/log(2)+1 and [weekday] <6and datediff(month, @calc_start_dt, dt)%@freq_recurrence_factor = 0;
if @freq_interval=10 -- weekend
insert into @tblselect * from #tblCalendar
where datediff(day, @max_start_date, dt) >=0 and datediff(day, dt, @min_end_date)>=0
and week_of_month = log(@freq_relative_interval)/log(2)+1and [weekday] in (6, 7)
and datediff(month, @calc_start_dt, dt)%@freq_recurrence_factor = 0;
endelse -- @freq_relative_interval = 16 -- last
begin -- @freq_relative_interval = 16
if @freq_interval < 8 begin; with c as (
select dt, [year], [month], [weekday], [day], [week_of_month], rn=row_number() over (partition by year, month order by week_of_month desc)
from #tblCalendarwhere dt between @calc_start_dt and @calc_end_dt
)
insert into @tblselect dt, [year], [month], [weekday], [day], [week_of_month] from c
where datediff(day, @max_start_date, dt) >=0 and datediff(day, dt, @min_end_date)>=0
and week_of_month = log(@freq_relative_interval)/log(2)+1and [weekday] = case @freq_interval when 1 then 7 else @freq_interval -1 end
and rn=1and datediff(month, @calc_start_dt, dt)%@freq_recurrence_factor = 0;
end if @freq_interval = 8 begin; with c as (
select dt, [year], [month], [weekday], [day], [week_of_month], rn=row_number() over (partition by year, month order by [day] desc)
from #tblCalendarwhere dt between @calc_start_dt and @calc_end_dt
)
insert into @tblselect dt, [year], [month], [weekday], [day], [week_of_month] from c
where datediff(day, @max_start_date, dt) >=0 and datediff(day, dt, @min_end_date)>=0
and rn =1and datediff(month, @calc_start_dt, dt)%@freq_recurrence_factor = 0;
endif @freq_interval=9 -- weekday
begin; with c as (
select dt, [year], [month], [weekday], [day], [week_of_month], rn=row_number() over (partition by year, month order by week_of_month desc)
from #tblCalendarwhere dt between @calc_start_dt and @calc_end_dt
)
insert into @tblselect dt, [year], [month], [weekday], [day], [week_of_month] from c
where datediff(day, @max_start_date, dt) >=0 and datediff(day, dt, @min_end_date)>=0
and [weekday] <6 and rn=1
and datediff(month, @calc_start_dt, dt)%@freq_recurrence_factor = 0;
endif @freq_interval=10 -- weekend
begin; with c as (
select dt, [year], [month], [weekday], [day], [week_of_month], rn=row_number() over (partition by year, month order by week_of_month desc)
from #tblCalendarwhere dt between @calc_start_dt and @calc_end_dt
)
insert into @tblselect dt, [year], [month], [weekday], [day], [week_of_month] from c
where datediff(day, @max_start_date, dt) >=0 and datediff(day, dt, @min_end_date)>=0
and [weekday] in (6, 7) and rn=1
and datediff(month, @calc_start_dt, dt)%@freq_recurrence_factor = 0;
endend -- @freq_relative_interval = 16
end -- monthly, @freq_type = 32
if exists (select * from @tbl) -- we do have days that match the schedules, now we need to loop through time to check whether the job will be run
begindeclare curT_daily cursor for
select dt from @tbl;
open curT_daily;fetch next from curT_daily into @dt;
while @@fetch_status =0
begin -- while loop
set @inner_loop_dt = dateadd(second, @active_start_time_int/10000*3600+@active_start_time_int%10000/100*60+@active_start_time_int%100, @dt);
while @inner_loop_dt <= dateadd(second, @active_end_time_int/10000*3600+@active_end_time_int%10000/100*60+@active_end_time_int%100, @dt)
beginif @inner_loop_dt between @max_start_date and @min_end_date
insert into #job (jobname, ScheduleName, run_datetime) select jobname, @Schedule_Name, @inner_loop_dt from #tmp where schedule_id = @schedule_id if @freq_subday_type = 1 begin set @inner_loop_dt = @inner_loop_dt + 1; continue; end if @freq_subday_type = 2set @inner_loop_dt = dateadd(second, @freq_subday_interval, @inner_loop_dt);
else if @freq_subday_type = 4
set @inner_loop_dt = dateadd(MINUTE, @freq_subday_interval, @inner_loop_dt);
elseset @inner_loop_dt = dateadd(HOUR, @freq_subday_interval, @inner_loop_dt);
endfetch next from curT_daily into @dt;
end -- while loop
close curT_daily; deallocate curT_daily; endend -- -- else not one time only
fetch next from curSch into @schedule_id, @schedule_name, @active_start_dt, @active_end_dt
, @freq_type, @freq_interval
, @freq_subday_type
, @freq_subday_interval
, @freq_relative_interval
, @freq_recurrence_factor
, @active_start_time_int
, @active_end_time_int
, @next_run_date
, @next_run_time;
end -- loop
close curSch; deallocate curSch;select ServerName, JobName, ScheduleName, Run_DateTime from #job
end -- proc
goSo how can we use this code in a more innovative way other than finding the future job scheduled times? Here are a few user cases:
1. You need to check whether there is any job not running at an expected datetime.
A job can miss its execution because the last running is past the current schedule time. For example, if a job is scheduled to run every 10 min and usually it takes only 5 min to finish the run, now if the job (run at 10:00am) suddenly runs 12 min, then the expected 10:10am run will not start at all. There are some other weird incidents that can cause the job not running, like failover happened and caused sql agent service not started during the time that the job is scheduled to run etc.
2. You need to make sure a job indeed runs.
I have seen in some environments, some jobs are configured with a completion notification, i.e. whether the job succeeds or fails, an email alert will be sent. But 99% of the time, the job succeeds, yet, as a DBA, I still need to bear the pain to receive and delete a job saying “The job xxxx completes”. What I really want is if the job fails, send me an alert, otherwise, do not send me anything. But I need to make sure the job indeed runs. The job schedule can be something weird, like the every 3 months, the 3rd weekend days from 10:00am to 10:00pm every 2 hours.
The solution: Every 1st day of each month (or any day you like), I will run the script and dump the result to a table, let’s call it tblJobFutureSchedule, and then at the end of every day (or every hour/week to your own choice), I will run a check script to compare the result in the msdb.dbo.sysjobhistory with tblJobFutureSchedule to make sure (1) there is no missing job execution and (2) the job indeed runs. If issue found, an alert will be sent, and we can do further investigation.
3. If there is a scheduled server patching work (implying that there are multiple reboots during the patching process), we can use the code to generate a list of jobs that will be scheduled to run during the patching window, so we can disable these jobs before the patching starts and later enable these jobs after the patching window.
Attachment:uspCheckJobFutureSchedule
