The TImes, They Are A-Changing

No, not the song. Nor the reference in Watchmen. But the clocks. Y’know, that primitive thing where we have to put the clocks back an hour in the winter and push them forward an hour in the spring? That.

What does this have to do with SQL Server? Well… you know that SQL Agent thing that schedules tasks for you? What does *that* do?

Disclaimer

This is a work-in-progress. The post was put together in a hurry while failing to sleep at a conference, and the logic was written one lunchtime. I know I’m missing something somewhere, but I haven’t yet figured out what all of it is. And it relies on the new(ish) DATE and TIME data types, so won’t be any good for earlier (or “unsupported”) versions of SQL Server. The idea behind this was based on some other blog / forum posts that I haven’t looked for again while writing this. I guess what I’m saying here is DON’T RELY ON THIS WITHOUT VERIFYING IT FOR YOURSELVES. Sheesh.

Spring Forward

At 1am the time jumps straight to 2am. Got any jobs scheduled to run at 01:30? They ain’t going to happen. I hope they weren’t important.

Fall Back

Autumn Back? Eh… This is a bit more complicated. When a job runs on a schedule, the scheduler calculates the next time the job is to run. If you’ve got a job to run every 15 minutes, it’ll run at 1:45, but it’ll calculate the time of the next run be 2am. Except a second after 01:59:59 the clock goes back to 1am, and your job won’t run again until 2am. Got any transaction logs to be backed up in that hour? Again, ain’t going to happen, and your RTOs have just gone phut.

What jobs are affected

I don’t know about you, but I have several hundred servers, and I don’t fancy checking the Agent schedule on each and every one of them individually. There must be a way to query to see which tasks are scheduled to be run between 1am & 2am, I thought.

The tables we need to look at are in the msdb database. They are:

  • sysjobs – a table containing some useful information about SQL Server Agent scheduled tasks.
  • sysschedules – a table containing schedule information
  • sysjobschedules – a table that links a job to a schedule

I think you can see where this is going, and this is a basic query to see what the jobs and their schedules are:

USE msdb;
 
SELECT  j.name AS JobName ,
        sj.next_run_date ,
        CONVERT(DATE, CONVERT(VARCHAR(8), next_run_date)) ,
        sj.next_run_time ,
        CONVERT(TIME, LEFT(RIGHT('000000'
                                 + CONVERT(VARCHAR(6), sj.next_run_time), 6),
                           2) + ':' + SUBSTRING(RIGHT('000000'
                                                      + CONVERT(VARCHAR(6), sj.next_run_time),
                                                      6), 3, 2) + ':'
        + RIGHT(RIGHT('000000' + CONVERT(VARCHAR(6), sj.next_run_time), 6), 2)) ,
        20160327 - sj.next_run_date ,
        DATEDIFF(DAY, CONVERT(DATE, '2016-03-27'),
                 CONVERT(DATE, CONVERT(VARCHAR(8), sj.next_run_date))) ,
        s.* ,
        CONVERT(TIME, LEFT(RIGHT('000000'
                                 + CONVERT(VARCHAR(6), s.active_start_time), 6),
                           2) + ':' + SUBSTRING(RIGHT('000000'
                                                      + CONVERT(VARCHAR(6), s.active_start_time),
                                                      6), 3, 2) + ':'
        + RIGHT(RIGHT('000000' + CONVERT(VARCHAR(6), s.active_start_time), 6),
                2)) AS TimeActiveStartTime
FROM    sysjobs j
        LEFT JOIN sysjobschedules sj ON sj.job_id = j.job_id
        LEFT JOIN dbo.sysschedules s ON s.schedule_id = sj.schedule_id
WHERE   s.enabled = 1
        AND j.enabled = 1
        AND sj.next_run_date  0
ORDER BY j.name;

Yeah, not quite as simple as we had hoped.

In reverse order, the WHERE clause:

  • s.enabled – jobs with schedules that are enabled
  • j.enabled – jobs that are enabled – no, this isn’t the same – you can have a job enabled that doesn’t have an active schedule
  • sj.next_run_date 0 – jobs that have a future run date

And in the SELECT clause, we have to do some unpleasant transformations to translate the internal representations of the dates and times into things that actually *are* SQL Server dates and times. Yuck.

However, all that query does, is show what jobs currently have a schedule.

To find jobs that are scheduled to run some time between 1am and 2am on 27 March 2016, we then need to do some filtering, in two stages:

  • Jobs due to run on the right day – found by examining the freq_type, freq_interval and freq_relative_interval fields
    • Single-shot jobs. freq_type = 1. We can just check the date_next_run field here for 2016-03-27. Easy
    • every n Days jobs. freq_type = 4; freq_interval = non-zero. A bit trickier – we need to check where there are a multiple of freq_intervals between the next_run_date and the target date, hence use of the modulo (%) operator.
    • Jobs on a weekly schedule. freq_type = 8; freq_interval must involve a 1 (for Sunday); valid values include 1 (for just Sunday), 65 (for Saturdays and Sundays), 127 (for every day). Use the & operator to perform a Bitwise AND operation
    • Monthly, freq_type = 16, on freq_interval day of the month – easy to check
    • Monthly, relative. freq_type = 32, freq_interval =1 (for Sunday) [NB: need to write check for freq_interval = 10 for weekend days – going to be tricky]; freq_relative_interval = 24 (for either the 4th or the last Sunday) – again, use Bitwise AND
  • Jobs due to run at the right time – checking freq_subday_type
    • Job runs at specified time – check active_start_time (or the translation thereof) for jobs due to start where the hour is 1
    • Job runs at a frequency of minutes / seconds – check the hour part of the active_start_time is 0 or 1
    • Job runs on a frequency of hours – job needs start either at 01:xx, or needs to start at 00:xx and have a repeat time of 1.

Now, I know I’ve missed something, but here’s what I’ve got so far.

WITH    EnabledJobSchedules
          AS ( SELECT   j.name AS JobName ,
                        sj.next_run_date ,
                        CONVERT(DATE, CONVERT(VARCHAR(8), next_run_date)) AS DateNextRunDate ,
                        sj.next_run_time ,
                        CONVERT(TIME, LEFT(RIGHT('000000'
                                                 + CONVERT(VARCHAR(6), sj.next_run_time),
                                                 6), 2) + ':'
                        + SUBSTRING(RIGHT('000000'
                                          + CONVERT(VARCHAR(6), sj.next_run_time),
                                          6), 3, 2) + ':'
                        + RIGHT(RIGHT('000000'
                                      + CONVERT(VARCHAR(6), sj.next_run_time),
                                      6), 2)) AS TimeNextRunTime ,
                        s.* ,
                        CONVERT(TIME, LEFT(RIGHT('000000'
                                                 + CONVERT(VARCHAR(6), s.active_start_time),
                                                 6), 2) + ':'
                        + SUBSTRING(RIGHT('000000'
                                          + CONVERT(VARCHAR(6), s.active_start_time),
                                          6), 3, 2) + ':'
                        + RIGHT(RIGHT('000000'
                                      + CONVERT(VARCHAR(6), s.active_start_time),
                                      6), 2)) AS TimeActiveStartTime
               FROM     sysjobs j
                        LEFT JOIN sysjobschedules sj ON sj.job_id = j.job_id
                        LEFT JOIN dbo.sysschedules s ON s.schedule_id = sj.schedule_id
               WHERE    s.enabled = 1
                        AND j.enabled = 1
                        AND sj.next_run_date  0
             )
    SELECT  *
    FROM    EnabledJobSchedules
    WHERE   1 = 1
            --AND DATEPART(HOUR, TimeNextRunTime) = 1
            AND ( ( 1 = 0 )
                  OR ( freq_type = 1
                       AND EnabledJobSchedules.DateNextRunDate = CONVERT(DATE, '27 mar 2016')
                     ) -- One-shot jobs
                  OR ( freq_type = 4
                       AND DATEDIFF(DAY, CONVERT(DATE, '27 mar 2016'),
                                    DateNextRunDate) % freq_interval = 0
                     ) -- Every x days
                  OR ( freq_type = 8
                       AND freq_interval & 1 = 1
                     ) -- weekly on sunday
                  OR ( freq_type = 16
                       AND freq_interval = 27
                     ) -- Monthly on 27th
                  OR ( freq_type = 32
                       AND freq_interval & 1 = 1
                       AND freq_relative_interval & 24 = 24
                     ) -- 4th or last Sunday of the month
                )
            AND ( ( freq_subday_type = 1
                    AND DATEPART(HOUR, TimeActiveStartTime) = 1
                  )
                  OR ( freq_subday_type = 8
                       AND ( ( DATEPART(HOUR, TimeActiveStartTime) = 1 )
                             OR ( freq_subday_interval = 1
                                  AND DATEPART(HOUR, TimeActiveStartTime) <= 1
                                )
                           )
                     )
                  OR ( freq_subday_type IN ( 2, 4 )
                       AND DATEPART(HOUR, TimeActiveStartTime) <= 1
                     )
                )
    ORDER BY JobName;

My question is – what am I missing? Only time will tell (hah!)

Advertisements
This entry was posted in SQLServerPedia Syndication and tagged , , . Bookmark the permalink.

One Response to The TImes, They Are A-Changing

  1. Pingback: Check Your Clocks – Curated SQL

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s