Summary

SQL Server Agent allows us to create jobs and assign any number of schedules to them. This allows for great flexibility when deciding when jobs should run. Over time, as the number of jobs and schedules increase on a given SQL Server, it can become challenging to fully understand when and how often jobs run during a given span of time.

A common need is to be able to quickly generate a list of jobs that will run during a given time frame. Knowing what jobs run when (and how often) can allow us to better plan maintenance events and ensure we do not miss anything important when SQL Server Agent is not running.

Our goal in this article will be to build a stored procedure that will generate that job run list for any time frame and provide as much detail as we need when executed. While this sounds simple enough, we will need to delve into job schedules within MSDB, which will inherently be a bit messy.

Prerequisites

A calendar table will be used in our code to allow us to quickly parse dates and assign metrics such as day of week and week of month. Here, we’ll use the calendar table introduced in a previous SQL Shack article: Designing a Calendar Table

If you already have your own calendar table that you are fond of, feel free to use it in place of this as the data is relatively easy to crunch and we are only using a few columns of the dimension table to get what we need.

Planning and Notes

Before diving in, let’s take a moment to plan our attack on this problem. What do we want to accomplish through our efforts here? What pitfalls should we watch out for while designing this?

All dates and times stored in MSDB are stored in the local server time zone. This means that sharing data across servers or converting from UTC may be problematic. To help keep this simple, we’ll build our process to accept and return data in both UTC and local time zones. This will ease the burden of converting between time zones, in the event that a server is not running under UTC time.

Jobs may be assigned many kinds of schedules. We’ll want to address all of them as follows:

Daily jobs that are run every day, or on a subset of days each week
Weekly jobs that are run each week on designated days
Monthly jobs that are run on a specific set of days during the month
One-time jobs that are intended to run once and never again
Jobs that run multiple times during any of the above schedules
Jobs that run at agent startup or when the server is idle (for reporting purposes)

We can return detailed data about each scheduled job run, but will also want to be able to roll that data up into a smaller data set. If a job runs every five minutes, then getting a row back per execution may be overkill if we examine a longer time period. As a result, the ability to return detail data or summarize will be useful in our result set.

Since a job can have multiple schedules, we should treat each job & schedule combination as a distinct entity. This will ensure we capture job runs triggered by all of the schedules used within a job.

One final bonus we can return with our results is the job schedule definitions for each job. This will allow us to easily understand why a job runs when it does and plan ahead to figure out what other time periods may look like.

The script attached to this article includes the calendar table creation as well as a full, working stored procedure that generates job schedules based on the code and snippets discussed below. Feel free to jump ahead to the download to use as a reference while reading this article.

MSDB Objects

SQL Server Agent jobs, schedules, and run history are all stored in the MSDB system database. The design of these tables hasn’t changed much over the years, and as a result their contents are a bit challenging to read. The following is a list of the schema within MSDB that we will use for our work:

Sysjobs: This table contains a row per job defined in SQL Server Agent. Included are columns that define the job name, description, if it is enabled, its category, start step, create/modified date, and a variety of other settings that impact overall job function.

Sysschedules: This table contains a row per schedule defined in SQL Server. Included are the name, description, if it is enabled, start/end dates, start end/times, and a variety of bitwise columns that describe how and when it runs. This table is inherently hard to read, so reviewing some of the less-obvious columns will be valuable here:

Freq_type: This will be a number from the following list that indicates how often the job runs: 1 = One-Time, 4 = Daily, 8 = Weekly, 16 = Monthly (on specific days), 32 = Monthly-Relative (On a given time of month, such as the 3^rd Tuesday or last Friday) , 64 = On SQL Server Agent startup, and 128 = When the server is idle
Freq_interval: If a schedule occurs every N days/weeks or in a given day/week of the month, then freq_interval will indicate that frequency of execution, otherwise it indicates which days of the week a job occurs. For days of the week, those days are given as a binary summation of: 1 = Sunday, 2 = Monday, 4 = Tuesday, 8 = Wednesday, 16 = Thursday, 32 = Friday, and 64 = Saturday. For example, 127 would indicate a job that runs every day whereas 13 would indicate a job that executes on Sunday, Tuesday, and Wednesday
Freq_subday_type: If 1, then a job runs at a specific time only. If a job runs every N seconds, minutes, or hours, then this will be set to 2 for seconds, 4 for minutes, or 8 for hours. 0 indicates that this is unused, such as for a job that runs when SQL Server Agent starts
Freq_subday_interval: If freq_subday_type indicates a job that runs every N seconds/minutes/hours, then this column will have a number that tells us how many seconds/minutes/hours will pass between job runs
Freq_relative_interval: If a job occurs on the Nth day of a month, then this indicates what N is. 0 = unused (for other schedule types), 1 = 1^st, 2 = 2^nd, 4 = 3^rd, 8 = 4^th, and 16 = last
Freq_recurrence_factor: If a job occurs every N weeks or months, then this column indicates what N is. 0 means it is not used for a given schedule type. This is only used for daily, weekly, or monthly schedules
Active_start_date & Active_end_date: These tell us when a job is active and will run. If the current date is outside of this range, then the job will not run
Active_start_time & Active_end_time: These provide boundaries of when the job should execute during the day. If the current time is not within this range, then the job will not run

Because this data is difficult to decipher, we’ll convert into easy-to-understand descriptions early in our process. This will make our code easier to understand, easier to customize, and reduce the chances of us making mistakes along the way.

Sysjobschedules: This is a linking table that relates jobs to schedules. A job may be referenced more than once in this table if it has multiple schedules.

Syscategories: This table contains a list of all categories defined within SQL Server Agent. Job categories can be used to classify jobs to make them easier to group and understand. We include this for informational purposes, but categories can be used to customize monitoring and alerting based on a job’s purpose or importance.

Agent_datetime (YYYYMMDD year, HHMMSS time): This function is not well documented, but converts the integer dates/times stored in many MSDB tables (such as sysschedules) into a DATETIME. This is a huge convenience and will save us the need to write a messy pile of TSQL to perform this conversion. For informational purposes, here is the TSQL behind this function:

RETURN CONVERT(DATETIME,

CONVERT(NVARCHAR(4), @date / 10000) + N'-' +

CONVERT(NVARCHAR(2), (@date % 10000)/100) + N'-' +

CONVERT(NVARCHAR(2), @date % 100) + N' ' +

CONVERT(NVARCHAR(2), @time / 10000) + N':' +

CONVERT(NVARCHAR(2), (@time % 10000)/100) + N':' +

CONVERT(NVARCHAR(2), @time % 100),120)

@date is YYYYMMDD stored as an integer and @time is HHMMSS, also stored as an integer.

Note that these objects are all in the dbo schema, and not in the sys schema as many other system objects are.

Determining Job Schedules

Before we start, let’s define the UTC offset for a given server. This will come in handy whenever we want to convert dates/times to UTC:

DECLARE @utc_offset INT;

SELECT

@utc_offset = -1 * DATEDIFF(HOUR, GETUTCDATE(), GETDATE());

To get the UTC offset in hours, all we need to do is find the difference between the local time and the UTC time. This offset will vary between servers that are set to different time zones, but the resulting UTC times will be consistent.

To figure out the job schedules for a given time period, we need to analyze the data from the MSDB views presented above and return an easier-to-digest format to work with:

SELECT

sysjobs.job_id,

sysschedules.schedule_uid,

sysjobs.name AS job_name,

CASE

WHEN sysschedules.freq_type = 1 THEN 'One-Time'

WHEN sysschedules.freq_type = 4 THEN 'Daily'

WHEN sysschedules.freq_type = 8 THEN 'Weekly'

WHEN sysschedules.freq_type = 16 THEN 'Monthly'

WHEN sysschedules.freq_type = 32 THEN 'Monthly-Relative'

WHEN sysschedules.freq_type = 64 THEN 'Agent Startup'

WHEN sysschedules.freq_type = 128 THEN 'Computer Idle'

END AS job_frequency,

sysschedules.freq_interval AS job_frequency_interval,

CASE

WHEN sysschedules.freq_subday_type = 0 THEN 'UNUSED'

WHEN sysschedules.freq_subday_type = 1 THEN 'AT_TIME'

WHEN sysschedules.freq_subday_type = 2 THEN 'SECONDS'

WHEN sysschedules.freq_subday_type = 4 THEN 'MINUTES'

WHEN sysschedules.freq_subday_type = 8 THEN 'HOURS'

END AS job_frequency_subday_type,

sysschedules.freq_subday_interval AS job_frequency_subday_interval,

CASE

WHEN sysschedules.freq_relative_interval = 0 THEN 'UNUSED'

WHEN sysschedules.freq_relative_interval = 1 THEN 'first'

WHEN sysschedules.freq_relative_interval = 2 THEN 'second'

WHEN sysschedules.freq_relative_interval = 4 THEN 'third'

WHEN sysschedules.freq_relative_interval = 8 THEN 'fourth'

WHEN sysschedules.freq_relative_interval = 16 THEN 'last'

END AS job_frequency_relative_interval,

sysschedules.freq_recurrence_factor AS job_frequency_recurrence_factor,

CAST(DATEADD(HOUR, @utc_offset, msdb.dbo.agent_datetime(sysschedules.active_start_date, sysschedules.active_start_time)) AS DATE) AS job_start_date_utc,

CAST(DATEADD(HOUR, @utc_offset, msdb.dbo.agent_datetime(sysschedules.active_start_date, sysschedules.active_start_time)) AS TIME(0)) AS job_start_time_utc,

CAST(DATEADD(HOUR, @utc_offset, msdb.dbo.agent_datetime(sysschedules.active_start_date, sysschedules.active_start_time)) AS DATETIME) AS job_start_datetime_utc,

sysjobs.date_created AS job_date_created,

sysschedules.date_created AS schedule_date_created,

'' AS job_schedule_description, -- To be populated later

CASE

WHEN sysschedules.freq_type = 1 THEN 1

WHEN sysschedules.freq_type = 4 THEN 1

WHEN sysschedules.freq_type = 8 THEN 1

WHEN sysschedules.freq_type = 16 THEN 1

WHEN sysschedules.freq_type = 32 THEN 1

WHEN sysschedules.freq_type = 64 THEN 0

WHEN sysschedules.freq_type = 128 THEN 0

END AS job_count

FROM msdb.dbo.sysjobschedules

INNER JOIN msdb.dbo.sysjobs

ON sysjobs.job_id = sysjobschedules.job_id

INNER JOIN msdb.dbo.sysschedules

ON sysschedules.schedule_id = sysjobschedules.schedule_id

INNER JOIN msdb.dbo.syscategories

ON syscategories.category_id = sysjobs.category_id

WHERE sysschedules.enabled = 1

AND sysjobs.enabled = 1;

This query takes metadata from the various MSDB tables that we introduced above and converts it in an easier-to-understand plain-text set of descriptions whenever possible. Here is what the output looks like:

There are more columns are off to the right, but we can get the general idea of what each schedule means via a quick glance at this data. For example, the first job has a schedule that has it run weekly on Monday, Wednesday, and Friday at a specific time each day. The last job on the list is set to run a single time and not recur. The last column in our data set, job_count, is 0 for jobs that run at startup or when the server’s CPU is idle. This is used later when counting expected job runs as jobs with these schedules will not run on a predictable schedule that can easily be mapped out ahead of time.

The job schedule description is set blank for now, but will be updated later as we begin populating schedule data and can more easily assess what each schedule entails based on its type.

Also note that since a job can be assigned any number of schedules, it is possible for a job to appear multiple times in the above list. Disabled jobs and disabled schedules are explicitly omitted, though these filters can easily be removed, if needed.

Our goal is to create a list of expected job executions. With a list of job schedules, we can do this, though we will need to take a different approach for each type of schedule. One-time, daily, weekly, monthly, and monthly (relative) each have enough differences that writing separate code for each is the simples way to accomplish our task. This will result in more TSQL, but it will be easier to debug, customize, and maintain over time.

As a convenience, we’ll place the results from above into a temporary table:

CREATE TABLE #job_summary

( job_id UNIQUEIDENTIFIER NOT NULL,

schedule_uid UNIQUEIDENTIFIER NOT NULL,

job_name VARCHAR(128) NOT NULL,

job_frequency VARCHAR(25) NOT NULL, -- ONE-TIME, DAILY, WEEKLY, MONTHLY, MONTHLY-RELATIVE, AGENT_STARTUP, COMPUTER_IDLE

job_frequency_interval INT NOT NULL,

job_frequency_subday_type VARCHAR(25) NOT NULL, -- UNUSED, AT_TIME, SECONDS, MINUTES, HOURS

job_frequency_subday_interval INT NOT NULL,

job_frequency_relative_interval VARCHAR(25) NOT NULL, -- UNUSED, FIRST, SECOND, THIRD, FOURTH, LAST

job_frequency_recurrence_factor INT NOT NULL,

job_start_date_utc DATE NOT NULL,

job_start_time_utc TIME NOT NULL,

job_start_datetime_utc DATETIME NOT NULL,

job_end_date_utc DATE NOT NULL,

job_end_time_utc TIME NOT NULL,

job_end_datetime_utc DATETIME NOT NULL,

job_date_created_utc DATETIME NOT NULL,

schedule_date_created_utc DATETIME NOT NULL,

job_schedule_description VARCHAR(250),

job_count INT NOT NULL,

PRIMARY KEY CLUSTERED (job_id, schedule_uid));

In addition, we’ll create a table that will contain a full list of job runs:

CREATE TABLE #future_job_runs

( job_id UNIQUEIDENTIFIER NOT NULL,

schedule_uid UNIQUEIDENTIFIER NOT NULL,

job_run_time_utc DATETIME NOT NULL,

job_run_time_local DATETIME NOT NULL,

PRIMARY KEY CLUSTERED (job_id, job_run_time_utc));

We’ll insert rows into this table in each section of this article as we build up a complete set of job runs. While this table may hold a large number of rows, it’s narrow enough so as to not consume any significant amount of space as we work through this challenge.

Some other variables we’ll use throughout our scripts:

@start_time_utc: The start of the time frame to generate job schedules for (in UTC).
@end_time_utc: The end of the time frame to generate job schedules for (in UTC).
@start_time_local: The start of the time frame to generate job schedules for (in local server time).
@end_time_local: The end of the time frame to generate job schedules for (in local server time).
@return_summarized_data: A bit that will be 1 when we want a single summary row per job and 0 when we want a full list of all job runs.
@include_startup_and_idle_jobs_in_summary_data: When 1, will include a reference to jobs with schedules that run on Agent startup or when CPU is idle. When 0, these jobs will be ignored.
@end_date_local_int: This is a convenience value that is the YYYYMMDD integer representation of the end date. This will reduce the complexity of code in a section of our work later on.

These may be hard-coded or treated as parameters, depending on your use-case. We also will declare and populate a temporary table:

CREATE TABLE #days_affected

(calendar_date DATE NOT NULL PRIMARY KEY CLUSTERED);

INSERT INTO #days_affected

(calendar_date)

SELECT

Dim_Date.Calendar_Date

FROM dbo.Dim_Date

WHERE Dim_Date.Calendar_Date >= CAST(@start_time_utc AS DATE)

AND Dim_Date.Calendar_Date <= @end_time_utc;

This provides us date coverage for recurring jobs, as well as an easy join to a calendar table, when needed.

One-Time Schedules

These jobs run on a specified date and time and will not recur.

INSERT INTO #future_job_runs

(job_id, schedule_uid, job_run_time_utc, job_run_time_local)

SELECT

job_summary.job_id,

job_summary.schedule_uid,

job_summary.job_start_datetime_utc,

DATEADD(HOUR, @utc_offset * -1, job_summary.job_start_datetime_utc) AS job_run_time_local

FROM #job_summary job_summary

WHERE job_frequency = 'ONE-TIME'

AND job_summary.job_start_datetime_utc BETWEEN @start_time_utc AND @end_time_utc

AND job_summary.job_start_datetime_utc >= job_summary.job_date_created_utc

AND job_summary.job_start_datetime_utc >= job_summary.schedule_date_created_utc;

We will also update the job schedule description with an easily readable explanation of what the schedule means:

UPDATE job_summary

SET job_schedule_description = 'One time, at ' + CAST(job_summary.job_start_datetime_utc AS VARCHAR(MAX)) + ' UTC (' + CAST(DATEADD(HOUR, @utc_offset * -1, job_summary.job_start_datetime_utc) AS VARCHAR(MAX)) + ' local)'

FROM #job_summary job_summary

WHERE job_frequency = 'ONE-TIME';

For these jobs, we need only insert a single row at the appropriate time and we are all set!

Recurring Schedules

For schedules that can recur, we have additional work that will require us to iterate through schedules and time periods to ensure a full list of job runs is generated. To do this, we’ll declare a variety of variables that will be needed for different types of recurring jobs:

DECLARE @job_counter UNIQUEIDENTIFIER;

DECLARE @schedule_counter UNIQUEIDENTIFIER;

DECLARE @datetime_counter DATETIME;

DECLARE @job_frequency_subday_interval INT;

DECLARE @active_start_datetime DATETIME;

DECLARE @active_end_datetime DATETIME;

DECLARE @active_start_time TIME;

DECLARE @active_end_time TIME;

DECLARE @job_frequency_interval INT;

DECLARE @job_frequency_relative_interval VARCHAR(10);

DECLARE @day_of_week_in_month TINYINT;

We’ll review these in more detail as they are used. From here, we can address each type of schedule and how we can generate a run list for each one for whatever time period we throw at it.

Daily Schedules

These schedules represent jobs that run once per day on a given set of days or multiple times per day on a given set of days. For those that only occur once per day, we can generate schedules by cross joining our set of days that we are reporting over:

-- Daily, once per day

INSERT INTO #future_job_runs

(job_id, schedule_uid, job_run_time_utc, job_run_time_local)

SELECT

job_summary.job_id,

job_summary.schedule_uid,

CAST(days_affected.calendar_date AS DATETIME) + CAST(job_summary.job_start_time_utc AS DATETIME) AS job_run_time_utc,

DATEADD(HOUR, @utc_offset * -1, CAST(days_affected.calendar_date AS DATETIME) + CAST(job_summary.job_start_time_utc AS DATETIME)) AS job_run_time_local

FROM #job_summary job_summary

CROSS JOIN #days_affected days_affected

WHERE job_summary.job_frequency = 'DAILY'

AND job_summary.job_date_created_utc <= @start_time_utc

AND job_summary.schedule_date_created_utc <= @start_time_utc

AND CAST(days_affected.calendar_date AS DATETIME) + CAST(job_summary.job_start_time_utc AS DATETIME) BETWEEN @start_time_utc AND @end_time_utc

AND CAST(days_affected.calendar_date AS DATETIME) + CAST(job_summary.job_start_time_utc AS DATETIME) BETWEEN job_summary.job_start_datetime_utc AND job_summary.job_end_datetime_utc

AND job_summary.job_frequency_subday_type IN ('UNUSED', 'AT_TIME')

AND ((DATEPART(DW, days_affected.calendar_date) & job_summary.job_frequency_interval = 0 AND job_summary.job_frequency_interval > 1)

OR job_summary.job_frequency_interval = 1);

UPDATE job_summary

SET job_schedule_description = 'Daily' + CASE WHEN job_summary.job_frequency_interval > 1 THEN ', every ' + CAST(job_summary.job_frequency_interval AS VARCHAR(MAX)) + ' days' ELSE '' END +

', at ' + LEFT(CAST(job_summary.job_start_time_utc AS VARCHAR(MAX)), 8) + ' UTC (' + LEFT(CAST(DATEADD(HOUR, @utc_offset * -1, job_summary.job_start_time_utc) AS VARCHAR(MAX)), 8) + ' local)'

The result of the above query will be a row per day per job. The filters control a variety of timing constraints that are worth noting here:

The job should have been created prior to our start time. If the job did not exist yet, then a job run would not have occurred
The schedule should have been created prior to our start time. If the schedule did not exist yet, then jobs could not have used it
The job run day matches the set of days bounded by our start and end times
The job run time is within the hours that the job is supposed to run

These ensure we maintain a high level of accuracy with respect to each job’s intended runtime, as well as the create dates for jobs & schedules.

The next set of daily jobs to consider are those that run daily, but multiple times. These will be a bit more challenging to collect as we need to preserve the relative time intervals between each job run. For example, a job that runs every 7 hours will not occur at the same times each day, whereas a job that runs every 2 hours will.

-- Daily, every N hours, minutes, or seconds

IF EXISTS (SELECT * FROM #job_summary WHERE job_frequency = 'DAILY' AND job_frequency_subday_type IN ('SECONDS', 'MINUTES', 'HOURS'))

BEGIN

DECLARE job_cursor CURSOR FOR

SELECT

job_summary.job_id,

job_summary.schedule_uid,

DATEADD(DAY, -1, CAST(CAST(@start_time_utc AS DATE) AS DATETIME) + CAST(job_summary.job_start_time_utc AS DATETIME)) AS datetime_counter,

CASE

WHEN job_summary.job_frequency_subday_type = 'SECONDS' THEN job_summary.job_frequency_subday_interval

WHEN job_summary.job_frequency_subday_type = 'MINUTES' THEN job_summary.job_frequency_subday_interval * 60

WHEN job_summary.job_frequency_subday_type = 'HOURS' THEN job_summary.job_frequency_subday_interval * 3600

END AS job_frequency_subday_interval,

job_summary.job_start_datetime_utc,

job_summary.job_end_datetime_utc,

job_summary.job_start_time_utc,

job_summary.job_end_time_utc,

job_summary.job_frequency_interval

FROM #job_summary job_summary

WHERE job_summary.job_frequency = 'DAILY'

AND job_summary.job_date_created_utc <= @start_time_utc

AND job_summary.schedule_date_created_utc <= @start_time_utc

AND job_summary.job_frequency_subday_type IN ('SECONDS', 'MINUTES', 'HOURS');

OPEN job_cursor;

FETCH NEXT FROM job_cursor INTO @job_counter, @schedule_counter, @datetime_counter, @job_frequency_subday_interval,

@active_start_datetime, @active_end_datetime, @active_start_time, @active_end_time, @job_frequency_interval;

WHILE @@FETCH_STATUS = 0

BEGIN

WHILE @datetime_counter < @start_time_utc

BEGIN

SELECT @datetime_counter = DATEADD(SECOND, @job_frequency_subday_interval, @datetime_counter);

END

WHILE @datetime_counter <= @end_time_utc

BEGIN

IF (@datetime_counter BETWEEN @active_start_datetime AND @active_end_datetime)

AND ((@active_start_time <= @active_end_time AND CAST(@datetime_counter AS TIME) BETWEEN @active_start_time AND @active_end_time) OR

(@active_start_time > @active_end_time AND

((CAST(@datetime_counter AS TIME) BETWEEN @active_start_time AND '23:59:59.999') OR CAST(@datetime_counter AS TIME) BETWEEN '00:00:00' AND @active_end_time)))

AND ((DATEPART(DW, @datetime_counter) & @job_frequency_interval = 0 AND @job_frequency_interval > 1)

OR @job_frequency_interval = 1)

BEGIN

INSERT INTO #future_job_runs

(job_id, schedule_uid, job_run_time_utc, job_run_time_local)

SELECT

@job_counter,

@schedule_counter,

@datetime_counter AS job_run_time_utc,

DATEADD(HOUR, @utc_offset * -1, @datetime_counter) AS job_run_time_local;

END

SELECT @datetime_counter = DATEADD(SECOND, @job_frequency_subday_interval, @datetime_counter);

END

UPDATE job_summary

SET job_schedule_description = 'Daily' + CASE WHEN job_summary.job_frequency_interval > 1 THEN ', every ' + CAST(job_summary.job_frequency_interval AS VARCHAR(MAX)) + ' days' ELSE '' END +

', every ' + CASE WHEN @job_frequency_subday_interval >= 3600 THEN CAST(@job_frequency_subday_interval / 3600 AS VARCHAR(MAX)) + ' hour(s)'

WHEN @job_frequency_subday_interval >= 60 THEN CAST(@job_frequency_subday_interval / 60 AS VARCHAR(MAX)) + ' minute(s)'

ELSE CAST(@job_frequency_subday_interval AS VARCHAR(MAX)) + ' second(s)' END +

' starting at ' + LEFT(CAST(job_summary.job_start_time_utc AS VARCHAR(MAX)), 8) +

' UTC (' + LEFT(CAST(DATEADD(HOUR, @utc_offset * -1, job_summary.job_start_time_utc) AS VARCHAR(MAX)), 8) + ' local)' +

CASE WHEN @active_start_time <> '00:00:00' OR @active_end_time <> '23:59:59'

THEN ', between the hours of ' + LEFT(CAST(@active_start_time AS VARCHAR(MAX)), 8) +

' UTC and ' + LEFT(CAST(@active_end_time AS VARCHAR(MAX)), 8) + ' UTC' ELSE '' END +

CASE WHEN @active_start_datetime > @start_time_utc OR @active_end_datetime < @end_time_utc

THEN ', between the dates of ' + CAST(CAST(@active_start_datetime AS DATE) AS VARCHAR(MAX)) +

' and ' + CAST(CAST(@active_end_datetime AS DATE) AS VARCHAR(MAX)) ELSE '' END

FROM #job_summary job_summary

WHERE job_summary.job_id = @job_counter

AND job_summary.schedule_uid = @schedule_counter;

This code is a bit more complex as we need to perform the same analysis as above, but for each and every job run. Iterating through jobs and run times may not seem to be an efficient solution, but as table access is not a part of the run time looping, it executes quite quickly. An additional liberty taken at this point is to normalize job run frequency into seconds. This allows us to manage jobs that increment run times by hours, minutes, and seconds all in a single set of queries.

The end result of the above code will be a set of scheduled job runs for each job per day that it is run. For jobs that run very often, this can be a long list that may be too large to be of use on its own. Summarizing data will help resolve this and reduce a long run list into a single row with start and end datetimes, as well as expected run counts.

Weekly Schedules

Despite the name, weekly schedules may occur once or more than once per week on a given set of days. As a result, the code for this is very similar to that of daily schedules, with a single additional filter that will validate if a given date is in the set of days a job schedule is configured to run on.

The following is all of the code necessary to process weekly schedules:

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

INSERT INTO #future_job_runs

(job_id, schedule_uid, job_run_time_utc, job_run_time_local)

SELECT

job_summary.job_id,

job_summary.schedule_uid,

CAST(days_affected.calendar_date AS DATETIME) + CAST(job_summary.job_start_time_utc AS DATETIME) AS job_run_time_UTC,

DATEADD(HOUR, @utc_offset * -1, CAST(days_affected.calendar_date AS DATETIME) + CAST(job_summary.job_start_time_utc AS DATETIME)) AS job_run_time_local

FROM #job_summary job_summary

CROSS JOIN #days_affected days_affected

WHERE job_frequency = 'WEEKLY'

AND job_summary.job_date_created_utc <= @start_time_utc

AND job_summary.schedule_date_created_utc <= @start_time_utc

AND CAST(days_affected.calendar_date AS DATETIME) + CAST(job_summary.job_start_time_utc AS DATETIME) BETWEEN @start_time_utc AND @end_time_utc

AND CAST(days_affected.calendar_date AS DATETIME) + CAST(job_summary.job_start_time_utc AS DATETIME) BETWEEN job_summary.job_start_datetime_utc AND job_summary.job_end_datetime_utc

AND job_summary.job_frequency_subday_type IN ('UNUSED', 'AT_TIME')

AND job_summary.job_frequency_interval & POWER(2, DATEPART(DW, days_affected.calendar_date) - 1) = POWER(2, DATEPART(DW, days_affected.calendar_date) - 1);

UPDATE job_summary

SET job_schedule_description = 'Weekly, on ' + LEFT(CASE WHEN job_summary.job_frequency_interval & 1 = 1 THEN 'Sunday, ' ELSE '' END +

CASE WHEN job_summary.job_frequency_interval & 2 = 2 THEN 'Monday, ' ELSE '' END +

CASE WHEN job_summary.job_frequency_interval & 4 = 4 THEN 'Tuesday, ' ELSE '' END +

CASE WHEN job_summary.job_frequency_interval & 8 = 8 THEN 'Wednesday, ' ELSE '' END +

CASE WHEN job_summary.job_frequency_interval & 16 = 16 THEN 'Thursday, ' ELSE '' END +

CASE WHEN job_summary.job_frequency_interval & 32 = 32 THEN 'Friday, ' ELSE '' END +

CASE WHEN job_summary.job_frequency_interval & 64 = 64 THEN 'Saturday, ' ELSE '' END, LEN(CASE WHEN job_summary.job_frequency_interval & 1 = 1 THEN 'Sunday, ' ELSE '' END +

CASE WHEN job_summary.job_frequency_interval & 2 = 2 THEN 'Monday, ' ELSE '' END +

CASE WHEN job_summary.job_frequency_interval & 4 = 4 THEN 'Tuesday, ' ELSE '' END +

CASE WHEN job_summary.job_frequency_interval & 8 = 8 THEN 'Wednesday, ' ELSE '' END +

CASE WHEN job_summary.job_frequency_interval & 16 = 16 THEN 'Thursday, ' ELSE '' END +

CASE WHEN job_summary.job_frequency_interval & 32 = 32 THEN 'Friday, ' ELSE '' END +

CASE WHEN job_summary.job_frequency_interval & 64 = 64 THEN 'Saturday, ' ELSE '' END) - 1) +

' at ' + LEFT(CAST(job_summary.job_start_time_utc AS VARCHAR(MAX)), 8) + ' UTC (' + LEFT(CAST(DATEADD(HOUR, @utc_offset * -1, job_summary.job_start_time_utc) AS VARCHAR(MAX)), 8) + ' local)'

FROM #job_summary job_summary

WHERE job_frequency = 'WEEKLY'

AND job_summary.job_frequency_subday_type IN ('UNUSED', 'AT_TIME');

-- Schedules that are weekly, but every N seconds, minutes, or hours.

IF EXISTS (SELECT * FROM #job_summary WHERE job_frequency = 'WEEKLY' AND job_frequency_subday_type IN ('SECONDS', 'MINUTES', 'HOURS'))

BEGIN

DECLARE job_cursor CURSOR FOR

SELECT

job_summary.job_id,

job_summary.schedule_uid,

DATEADD(DAY, -1, CAST(CAST(@start_time_utc AS DATE) AS DATETIME) + CAST(job_summary.job_start_time_utc AS DATETIME)),

CASE

WHEN job_summary.job_frequency_subday_type = 'SECONDS' THEN job_summary.job_frequency_subday_interval

WHEN job_summary.job_frequency_subday_type = 'MINUTES' THEN job_summary.job_frequency_subday_interval * 60

WHEN job_summary.job_frequency_subday_type = 'HOURS' THEN job_summary.job_frequency_subday_interval * 3600

END,

job_summary.job_frequency_interval,

job_summary.job_start_datetime_utc,

job_summary.job_end_datetime_utc,

job_summary.job_start_time_utc,

job_summary.job_end_time_utc

FROM #job_summary job_summary

WHERE job_summary.job_frequency = 'WEEKLY'

AND job_summary.job_date_created_utc <= @start_time_utc

AND job_summary.schedule_date_created_utc <= @start_time_utc

AND job_summary.job_frequency_subday_type IN ('SECONDS', 'MINUTES', 'HOURS');

OPEN job_cursor;

FETCH NEXT FROM job_cursor INTO @job_counter, @schedule_counter, @datetime_counter, @job_frequency_subday_interval, @job_frequency_interval,

@active_start_datetime, @active_end_datetime, @active_start_time, @active_end_time;

WHILE @@FETCH_STATUS = 0

BEGIN

WHILE @datetime_counter < @start_time_utc

BEGIN

SELECT @datetime_counter = DATEADD(SECOND, @job_frequency_subday_interval, @datetime_counter);

END

WHILE @datetime_counter <= @end_time_utc

BEGIN

IF @job_frequency_interval & POWER(2, DATEPART(DW, @datetime_counter) - 1) = POWER(2, DATEPART(DW, @datetime_counter) - 1)

AND (@datetime_counter BETWEEN @active_start_datetime AND @active_end_datetime)

AND ((@active_start_time <= @active_end_time AND CAST(@datetime_counter AS TIME) BETWEEN @active_start_time AND @active_end_time) OR

(@active_start_time > @active_end_time AND

((CAST(@datetime_counter AS TIME) BETWEEN @active_start_time AND '23:59:59.999') OR CAST(@datetime_counter AS TIME) BETWEEN '00:00:00' AND @active_end_time)))

BEGIN

INSERT INTO #future_job_runs

(job_id, schedule_uid, job_run_time_utc, job_run_time_local)

SELECT

@job_counter,

@schedule_counter,

@datetime_counter AS job_run_time_utc,

DATEADD(HOUR, @utc_offset * -1, @datetime_counter) AS job_run_time_local