Ed Pollack

Generating Schedules with SQL Server Agent

January 15, 2019 by

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 3rd 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 = 1st, 2 = 2nd, 4 = 3rd, 8 = 4th, 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:

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

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:

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:

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

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:

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.

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

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:

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:

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.

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:

As before, we handle schedules that occur once-per-day separately from those that recur within each day. An additional filter to ensure we only include job runs on the correct days is to compare the bitwise job_frequency_interval with the day of the week:

If the bitwise sum can be bitwise divided evenly by two raised to the power of the numeric value of the day of the week minus 1, then the job is supposed to run on that day. For example, consider a job that is expected to run on Sunday, Wednesday, and Friday. Those days are represented by 1, 4, and 6, respectively. For this schedule, job_frequency_interval will be 41 (0101001). When we check days of the week against that binary number, those that hit a one in the digits will be included in the schedules run times, whereas those with zeroes will not.

Monthly Schedules

These are quite similar to daily schedules in that they run on a given day of the month and may run once or multiple times on that given day. Since only a single day per month is affected, our code is simpler and we do not need to check bitwise values to determine what days a job should run on (what a relief!):

While the use-case for a schedule that runs multiple times per day monthly is odd, we include it for completeness as SQL Server does allow us to do unusual things like that. In theory, there could be a need to have a unique job recur once per month for additional coverage over the course of a significant day.

Monthly-Relative Schedules

Whereas monthly schedules occur on a specific day within the month, such as the 1st day, last day, or the 15th, relative schedules occur on a given instance of a day per month, such as the 2nd Tuesday, 4th Wednesday, or last Friday. As a result, the exact date will vary from month-to-month. This is the schedule type where a calendar table is very handy and will save us a ton of work when processing these schedules!

The following code is broken into similar sections as earlier: One for jobs that occur a single time on a given day and another for those that repeat within the target day:

The primary difference between these schedules and previous is the need to check a calendar table to determine what date matches the scheduling clause. The 1st Tuesday of February could be February 1st or it could be the 7th. The CASE statement that checks this is not terribly pretty, but converts FIRST, SECOND, THIRD, FOURTH, and LAST into numbers that can be compared against the day-of-week-in-month for any given date. Fortunately, since the date range we are analyzing are more likely to be a small set of days (and not numbering millions of days), performance here is not going to be an issue.

Startup and CPU Idle Schedules

These are special schedules that are included here for completeness and are optional. The bit @include_startup_and_idle_jobs_in_summary_data is used to filter them out if they are not needed.

This data is relatively simple to generate and allows us to maintain insight into jobs that may matter to us under special circumstances. If not, they can easily be omitted via the bit flag or removed from code altogether if there will never be a need for them.

Using Our Creation

When we string all of this code together into a stored procedure, we can run it on any SQL Server for a time period and get back a job listing. The parameters introduced earlier will be used as stored procedure parameters so that we can quickly execute this from whatever target maintenance database we wish.

Here is an execution of the final stored proc on my local server, which contains a variety of test jobs and schedules:

The results are as follows:

Since I passed in local times as parameters, the results are coordinated to those times, though UTC is also returned for informational purposes. The results are summarized and include the first and last run times for each job, as well as the total job count and a friendly description of the job schedule.

We can adjust the proc execution to include startup & idle CPU jobs, like this:

The results are the same as the previous example, with the addition of rows for the idle CPU and startup jobs. Note that the job run count is zero for each of these jobs as there is no predictable execution during the time period specified. Despite that, knowing those jobs exist can be useful when planning (or recovering) from maintenance.

Similarly, we can enter times in UTC:

The results will be identical to the previous example as the UTC times provided are equivalent to the local times on my server.

Lastly, we can expand the job run times so that they are not summarized. This can be useful if we want to crunch our own metrics, or if the schedules run list is short enough to eyeball manually:

In this example, a total of 80 rows were returned, one per distinct job execution. This can be a large data set, so summarizing will typically be the desired way to return this data for most use-cases.

With this tool, we can map out expected job executions for any time period, past or future. This can have many applications, such as:

  • Planning maintenance and ensuring that no jobs are missed while the SQL Server Agent service is not running
  • Looking back at an outage and determining if any significant job runs were missed
  • Correlating performance or error conditions with Agent job schedules
  • Understanding job schedules and usage of SQL Server Agent

Exceptions and Customization

Some jobs fall outside of the useful bounds of this process and we may want to omit them via changes to our code. Common jobs that we would want to ignore are:

  • Always-running jobs: Some jobs are built with frequent schedules but are continuously running. As a result, they will appear to the operator as frequent or missed jobs, but in reality, they are running normally. While an odd design pattern, you may wish to omit jobs such as these from the results to avoid confusion
  • Jobs that are resilient: Some jobs are built to rerun and catch up with any missed work. As a result, we may not care when they run, so long as they eventually succeed
  • Jobs that run very often: These are probably resilient jobs that are built to withstand errors or missed runs and we may not care as much about their schedules

As with any maintenance tool, we should also consider opportunities to customize. The inputs, filters, and outputs of the stored procedure are somewhat arbitrary. Additions can easily be made without having to dig too deeply into the code.

Different organizations have different data needs and some servers may specialize in specific types of jobs. This may lead to a need to track job importance or priority, which could easily be added as a dimension to this data.

Adjusting how we handle disabled jobs or schedules, or job/schedule creation times is as easy as removing filters on each section of code.

Conclusion

Being able to quickly map out all expected job runs for a given time period can be a huge time saver. We can predict future SQL Server Agent schedules, more confidently plan maintenance, and respond to outages with a better knowledge of what was missed.

The natural next step for this stored procedure is to compare its results to SQL Server Agent’s job history in order to automate a process that can check for missed jobs and alert an operator when this happens.

Heavy use of bitwise math may make this code intimidating, but customization will largely avoid the need to interact with any complex logic. Most meaningful changes can be accomplished by adding or removing simple filters from the initial job summarization, or by adjusting filters common to each other section of code throughout the process.

References and Further Reading

A calendar table is required by this process. While we can code around this, the results would be much longer and sloppier. Code for my calendar table is included in this article and you may learn more about its creation and use here: Designing a Calendar Table

msdb.dbo.sysschedules is one of the more complicated system tables. The following MSDN link explains it in far more detail, which can be helpful when customizing this code: dbo.sysschedules (Transact-SQL)

T-SQL script used in this article

Table of contents

Generating Schedules with SQL Server Agent
Detecting and Alerting on SQL Server Agent Missed Jobs

Ed Pollack

Ed Pollack

Ed has 20 years of experience in database and systems administration, developing a passion for performance optimization, database design, and making things go faster.He has spoken at many SQL Saturdays, 24 Hours of PASS, and PASS Summit.This lead him to organize SQL Saturday Albany, which has become an annual event for New York’s Capital Region.

In his free time, Ed enjoys video games, sci-fi & fantasy, traveling, and being as big of a geek as his friends will tolerate.

View all posts by Ed Pollack
Ed Pollack
Jobs, Maintenance

About Ed Pollack

Ed has 20 years of experience in database and systems administration, developing a passion for performance optimization, database design, and making things go faster. He has spoken at many SQL Saturdays, 24 Hours of PASS, and PASS Summit. This lead him to organize SQL Saturday Albany, which has become an annual event for New York’s Capital Region. In his free time, Ed enjoys video games, sci-fi & fantasy, traveling, and being as big of a geek as his friends will tolerate. View all posts by Ed Pollack

595 Views