Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Decode SQL Server Job Schedules

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;

go


create proc dbo.uspCheckJobFutureSchedule

  @Start_DT datetime

, @End_DT datetime

, @JobName varchar(128)=''

, @ScheduleName varchar(128)=''

as

begin --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 s

    inner join msdb.dbo.sysjobschedules js

    on js.schedule_id = s.schedule_id

    inner 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 > 0

    and 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 window


    create 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_id

        end -- one time only

        else

        begin -- 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_dt

            truncate table #tblCalendar;

            begin tran

            while @inner_loop_dt <= @calc_end_dt

            begin

                insert 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;

            end

            commit 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 = 16

        begin -- 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 = 32

        begin -- 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 #tblCalendar

                        where 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)+1

                    and datediff(month, @calc_start_dt, dt)%@freq_recurrence_factor = 0;

                end

                if @freq_interval = 8

                    insert into @tbl

                    select * 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 @tbl

                    select * 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] <6

                    and datediff(month, @calc_start_dt, dt)%@freq_recurrence_factor = 0;


                if @freq_interval=10 -- weekend

                    insert into @tbl

                    select * 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] in (6, 7)

                    and datediff(month, @calc_start_dt, dt)%@freq_recurrence_factor = 0;

            end

            else -- @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 #tblCalendar

                        where dt between @calc_start_dt and @calc_end_dt

                        )

                    insert into @tbl

                    select 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)+1

                    and [weekday] = case @freq_interval when 1 then 7 else @freq_interval -1 end

                    and rn=1

                    and 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 #tblCalendar

                        where dt between @calc_start_dt and @calc_end_dt

                        )

                    insert into @tbl

                    select 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 =1

                    and datediff(month, @calc_start_dt, dt)%@freq_recurrence_factor = 0;

                end

                if @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 #tblCalendar

                        where dt between @calc_start_dt and @calc_end_dt

                        )

                    insert into @tbl

                    select 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;

                end

                if @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 #tblCalendar

                        where dt between @calc_start_dt and @calc_end_dt

                        )

                    insert into @tbl

                    select 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;

                end

            end    -- @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

        begin

            declare 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)

                    begin


                        if @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 = 2

                            set @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);

                            else

                                set @inner_loop_dt = dateadd(HOUR, @freq_subday_interval, @inner_loop_dt);

                    end

                fetch next from curT_daily into @dt;

            end -- while loop


            close curT_daily;

            deallocate curT_daily;

        end


        end -- -- 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

go

So 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


Comments

Leave a comment on the original post [dbaphilosophy.wordpress.com, opens in a new window]

Loading comments...