摘要 (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.
SQL Server代理允许我们创建作业并为其分配任意数量的计划。 在决定何时运行作业时，这提供了极大的灵活性。 随着时间的推移，随着给定SQL Server上作业和计划的数量增加，要完全了解作业在给定时间段内的运行时间和频率会变得非常困难。
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.
通常需要能够快速生成将在给定时间范围内运行的作业列表。 了解什么作业在何时（以及何时运行）可以使我们更好地计划维护事件，并确保在SQL Server代理未运行时我们不会错过任何重要的事情。
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.
本文的目标是建立一个存储过程，该存储过程将在任何时间范围内生成该作业运行列表，并在执行时提供所需的详细信息。 尽管这听起来很简单，但是我们将需要深入研究MSDB中的作业计划，这本质上会有些混乱。
先决条件 (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
我们的代码中将使用日历表，以使我们能够快速解析日期并分配指标，例如星期几和每月的星期几。 在这里，我们将使用上一篇SQL Shack文章中介绍的日历表 ： 设计日历表
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.
MSDB中存储的所有日期和时间都存储在本地服务器时区中。 这意味着跨服务器共享数据或从UTC转换可能会出现问题。 为了简化操作，我们将构建流程以在UTC和本地时区接收和返回数据。 如果服务器未在UTC时间下运行，这将减轻在时区之间进行转换的负担。
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对象 (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:
SQL Server代理作业，日程表和运行历史记录都存储在MSDB系统数据库中。 这些表的设计多年来没有太大变化，因此，它们的内容很难读取。 以下是我们将用于工作的MSDB中的架构列表：
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.
Sysjobs ：该表在SQL Server代理中定义的每个作业包含一行。 包括定义工作名称，描述（如果已启用），其类别，开始步骤，创建/修改日期以及影响整体工作功能的各种其他设置的列。
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:
Sysschedules ：该表在SQL Server中定义的每个计划包含一行。 其中包括名称，描述（如果已启用），开始/结束日期，开始结束/时间，以及各种按位排列的列，描述了其运行方式和运行时间。 该表本质上很难阅读，因此在这里回顾一些不太明显的列将很有价值：
- 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_type ：这是下面列表中的一个数字，表示作业的运行频率：1 = 一次 ，4 = 每天 ，8 = 每周 ，16 = 每月 （在特定日期），32 = 每月相对 （在某天 ）给定月份的时候，比如第3个星期二或最后一个星期五），64 = 在SQL Server代理启动 ，和128 = 当服务器处于空闲状态
- Freq_interval: If a schedule occurs every N days/weeks or in a given day/week of the month, then Freq_interval ：如果计划每N天/周或每月的给定日期/周发生一次，则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_interval将指示执行频率，否则将指示作业在一周的哪几天发生。 对于一周中的几天，这些天以二进制加法给出：1 =星期日，2 =星期一，4 =星期二，8 =星期三，16 =星期四，32 =星期五和64 =星期六。 例如，127表示每天运行的作业，而13表示在星期日，星期二和星期三执行的作业
- 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_type ：如果为1，则作业仅在特定时间运行。 如果作业每N秒，分钟或小时运行一次，则将其设置为2（秒），4（分钟）或8（小时）。 0表示未使用它，例如对于SQL Server代理启动时运行的作业
- Freq_subday_interval: If Freq_subday_interva l：如果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_subday_type指示每隔N秒/分钟/小时运行一次作业，则此列将显示一个数字，告诉我们两次运行之间将经过多少秒/分钟/小时
- 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_relative_interval ：如果某工作在一个月的第N天发生，则表明N是多少。 0 =未使用的（对于其他调度类型），1 = 1，第2 = 2 次 ，4 = 3 日 ，8 = 第 4和第 16 =最后
- 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 Freq_recurrence_factor ：如果每N周或每月进行一次工作，则此列指示N是多少。 0表示不用于给定的日程表类型。 仅用于每日，每周或每月计划
- 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_date和Active_end_date ：这些信息可以告诉我们作业何时处于活动状态并可以运行。 如果当前日期不在此范围内，则该作业将无法运行
- 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 Active_start_time和Active_end_time ：这些提供了白天应在何时执行作业的界限。 如果当前时间不在此范围内，则作业将无法运行
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.
Sysjobschedules ：这是一个将作业与计划相关联的链接表。 如果作业具有多个计划，则在此表中可能会多次引用该作业。
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.
Syscategories ：该表包含SQL Server代理中定义的所有类别的列表。 作业类别可用于对作业进行分类，以使其更易于分组和理解。 我们出于信息目的将其包括在内，但可以根据工作目的或重要性使用类别来自定义监视和警报。
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:
Agent_datetime （YYYYMMDD年，HHMMSS时间）：该函数的文档记录不充分，但是会将许多MSDB表（例如sysschedules ）中存储的整数日期/时间转换为DATETIME。 这是一个极大的便利，将使我们不必编写一堆凌乱的TSQL来执行此转换。 仅供参考，以下是此函数后面的TSQL：
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.
@date是YYYYMMDD存储为整数，而@time是HHMMSS，也存储为整数。
Note that these objects are all in the dbo schema, and not in the sys schema as many other system objects are.
请注意，这些对象都在dbo模式中，而不是在sys模式中，就像许多其他系统对象一样。
确定工作时间表 (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:
在开始之前，让我们为给定服务器定义UTC偏移量。 每当我们要将日期/时间转换为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.
要获取以小时为单位的UTC偏移量，我们要做的就是找到本地时间与UTC时间之间的时差。 在设置为不同时区的服务器之间，此偏移量会有所不同，但是最终的UTC时间将保持一致。
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:
为了弄清楚给定时间段的工作计划，我们需要分析上面显示的MSDB视图中的数据，并返回一种易于理解的格式来进行处理：
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:
该查询从上面介绍的各种MSDB表中获取元数据，并在可能的情况下将其转换为易于理解的纯文本描述集。 输出结果如下所示：
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.
右边还有更多的列，但是通过快速浏览一下这些数据，我们可以大致了解每个时间表的含义。 例如，第一个作业有一个计划，该计划每周在星期一，星期三和星期五在每天的特定时间运行。 列表中的最后一个作业被设置为运行一次，并且不会重复发生。 对于启动时或服务器CPU空闲时运行的作业，数据集中的最后一列job_count为0。 稍后在计算预期的作业运行时将使用此功能，因为具有这些计划的作业将不会按可预测的时间表运行，而可以很容易地提前将其映射出来。
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.
我们的目标是创建一份预期的工作执行清单。 有了一份工作时间表列表，我们就可以做到这一点，尽管我们需要针对每种类型的时间表采用不同的方法。 一次，每天，每周，每月和每月（相对）都具有足够的差异，因此为每次编写单独的代码是完成任务的简单方法。 这将导致产生更多的TSQL，但随着时间的推移，它将更易于调试，自定义和维护。
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).
- @start_time_utc ：生成时间计划的时间范围的开始（以UTC为单位）。
- @end_time_utc: The end of the time frame to generate job schedules for (in UTC).
- @end_time_utc ：为（为UTC生成工作计划）的时间表的结束时间。
- @start_time_local: The start of the time frame to generate job schedules for (in local server time).
- @start_time_local ：为（在本地服务器时间内）生成作业计划的时间范围的开始。
- @end_time_local: The end of the time frame to generate job schedules for (in local server time).
- @end_time_local ：为（在本地服务器时间内）生成作业计划的时间范围的结束。
- @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.
- @return_summarized_data ：当我们希望每个作业有一个摘要行时，该位将为1；而当我们想要所有作业运行的完整列表时，则为0。
- @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.
- @include_startup_and_idle_jobs_in_summary_data ：为1时，将包括对具有在代理启动时或CPU空闲时运行的日程表的作业的引用。 当为0时，这些作业将被忽略。
- @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.
- @end_date_local_int ：这是一个方便值，它是结束日期的YYYYMMDD整数表示。 这将在以后的部分工作中降低代码的复杂性。
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.
下一组要考虑的日常工作是每天运行但多次运行的工作。 由于我们需要保留每个作业运行之间的相对时间间隔，因此收集这些数据将更具挑战性。 例如，每7小时运行一次的作业不会每天都在同一时间发生，而每2小时运行一次的作业将不会每天发生。
-- 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:
以下是处理每周计划所需的所有代码：
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
END
SELECT @datetime_counter = DATEADD(SECOND, @job_frequency_subday_interval, @datetime_counter);
END
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) +
', 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) +
' and ' + LEFT(CAST(@active_end_time AS VARCHAR(MAX)), 8) 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;
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;
END
CLOSE job_cursor;
DEALLOCATE job_cursor;
END
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:
和以前一样，我们将每天发生一次的计划与每天发生的计划分开处理。 确保我们只包括正确日期运行的另一个过滤器是将按位Job_frequency_interval与星期几进行比较：
AND job_summary.job_frequency_interval & POWER(2, DATEPART(DW, days_affected.calendar_date) - 1) = POWER(2, DATEPART(DW, days_affected.calendar_date) - 1);
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.
如果按位求和可以被除以2，然后按整数除以提高到星期几减1的数值的乘方，则该作业应该在该天运行。 例如，考虑预期在周日，周三和周五运行的作业。 那些日子分别用1、4和6表示。 对于此计划， job_frequency_interval将为41（0101001）。 当我们根据该二进制数检查一周中的几天时，那些在数字中达到1的数字将被包括在计划运行时间中，而那些具有零的数字将不包括在计划运行时间中。
每月时间表 (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!):
这些与每日时间表非常相似，因为它们在一个月的特定日期运行，并且在该特定日期可能运行一次或多次。 由于每月只影响一天，因此我们的代码更加简单，并且我们不需要检查按位值来确定工作应在哪几天进行（这很轻松！）：
DECLARE job_cursor CURSOR FOR
SELECT
job_summary.job_id,
job_summary.schedule_uid,
DATEADD(DAY, job_summary.job_frequency_interval - 1, DATEADD(DAY, -1 * DATEPART(DAY, CAST(@start_time_utc AS DATE)) + 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_frequency = 'MONTHLY'
AND job_frequency_subday_type IN ('SECONDS', 'MINUTES', 'HOURS')
AND job_frequency_recurrence_factor = 1
AND job_summary.job_frequency_relative_interval = 'UNUSED'
AND job_summary.job_date_created_utc <= @start_time_utc
AND job_summary.schedule_date_created_utc <= @start_time_utc;
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 (@datetime_counter BETWEEN @active_start_datetime AND @active_end_datetime)
AND DATEPART(DAY, @datetime_counter) = @job_frequency_interval
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
END
SELECT @datetime_counter = DATEADD(SECOND, @job_frequency_subday_interval, @datetime_counter);
END
UPDATE job_summary
SET job_schedule_description = 'Monthly, on day ' + CAST(job_summary.job_frequency_interval AS VARCHAR(MAX)) + ' of the month' +
', 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) +
' and ' + LEFT(CAST(@active_end_time AS VARCHAR(MAX)), 8) 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;
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;
END
CLOSE job_cursor;
DEALLOCATE job_cursor;
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.
虽然每月每天运行多次的日程表的用例很奇怪，但为了完整起见我们将其包括在内，因为SQL Server确实允许我们做类似的事情。 从理论上讲，可能需要每月重复一次独特的工作，以在重要的一天中进行额外的覆盖。
每月相对时间表 (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!
而每月时间表在一个月之内的某一天会发生，如第1天，最后一天，或15 日 ，发生在每月一天的给定实例相关的时间表，比如2 次周二4 日星期三，或上周五。 因此，确切的日期会因月份而异。 这是一种日程表类型，其中的日历表非常方便，在处理这些日程表时将为我们节省大量工作！
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:
以下代码分成与之前类似的部分：一个用于在给定日期一次执行的作业，另一个用于在目标日期内重复的作业：
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
INNER JOIN dbo.Dim_Date
ON days_affected.calendar_date = Dim_Date.Calendar_Date
WHERE job_frequency = 'MONTHLY-RELATIVE'
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 Dim_Date.Day_of_Week = job_summary.job_frequency_interval
AND job_summary.job_frequency_relative_interval IN ('FIRST', 'SECOND', 'THIRD', 'FOURTH', 'LAST')
AND Dim_Date.Day_of_Week_in_Month = CASE
WHEN job_summary.job_frequency_relative_interval = 'FIRST' THEN 1
WHEN job_summary.job_frequency_relative_interval = 'SECOND' THEN 2
WHEN job_summary.job_frequency_relative_interval = 'THIRD' THEN 3
WHEN job_summary.job_frequency_relative_interval = 'FOURTH' THEN 4
WHEN job_summary.job_frequency_relative_interval = 'LAST' THEN (SELECT MAX(MAX_CHECK.Day_of_Week_in_Month) FROM Dim_Date MAX_CHECK
WHERE MAX_CHECK.Calendar_Month = Dim_Date.Calendar_Month
AND MAX_CHECK.Calendar_Year = Dim_Date.Calendar_Year
AND MAX_CHECK.Day_of_Week = Dim_Date.Day_of_Week)
END
AND job_summary.job_frequency_recurrence_factor = 1;
UPDATE job_summary
SET job_schedule_description = 'Monthly, on the ' + job_summary.job_frequency_relative_interval +
CASE WHEN job_frequency_interval = 1 THEN ' Sunday' WHEN job_frequency_interval = 2 THEN ' Monday'
WHEN job_frequency_interval = 3 THEN ' Tuesday' WHEN job_frequency_interval = 4 THEN ' Wednesday'
WHEN job_frequency_interval = 5 THEN ' Thursday' WHEN job_frequency_interval = 6 THEN ' Friday'
WHEN job_frequency_interval = 7 THEN ' Saturday' END + ' of the month' +
' 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 = 'MONTHLY-RELATIVE'
AND job_summary.job_frequency_subday_type IN ('UNUSED', 'AT_TIME')
AND job_summary.job_frequency_recurrence_factor = 1;
-- Monthly schedules that run monthly on a given day, but multiple times.
IF EXISTS (SELECT * FROM #job_summary WHERE job_frequency = 'MONTHLY-RELATIVE' AND job_frequency_subday_type IN ('SECONDS', 'MINUTES', 'HOURS') AND job_frequency_recurrence_factor = 1)
BEGIN
DECLARE job_cursor CURSOR FOR
SELECT
job_summary.job_id,
job_summary.schedule_uid,
DATEADD(DAY, job_summary.job_frequency_interval - 1, DATEADD(DAY, -1 * DATEPART(DAY, CAST(@start_time_utc AS DATE)) + 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,
job_summary.job_frequency_relative_interval
FROM #job_summary job_summary
WHERE job_frequency = 'MONTHLY-RELATIVE'
AND job_frequency_subday_type IN ('SECONDS', 'MINUTES', 'HOURS')
AND job_frequency_recurrence_factor = 1
AND job_summary.job_frequency_relative_interval IN ('FIRST', 'SECOND', 'THIRD', 'FOURTH', 'LAST')
AND job_summary.job_date_created_utc <= @start_time_utc
AND job_summary.schedule_date_created_utc <= @start_time_utc;
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, @job_frequency_relative_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
SELECT @day_of_week_in_month = (SELECT Dim_Date.Day_of_Week_in_Month FROM dbo.Dim_Date WHERE Dim_Date.Calendar_Date = CAST(@datetime_counter AS DATE))
IF @job_frequency_interval = DATEPART(DW, @datetime_counter) 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)))
AND @day_of_week_in_month = CASE
WHEN @job_frequency_relative_interval = 'FIRST' THEN 1
WHEN @job_frequency_relative_interval = 'SECOND' THEN 2
WHEN @job_frequency_relative_interval = 'THIRD' THEN 3
WHEN @job_frequency_relative_interval = 'FOURTH' THEN 4
WHEN @job_frequency_relative_interval = 'LAST' THEN (SELECT MAX(MAX_CHECK.Day_of_Week_in_Month) FROM Dim_Date MAX_CHECK
WHERE MAX_CHECK.Calendar_Month = DATEPART(MONTH, @datetime_counter)
AND MAX_CHECK.Calendar_Year = DATEPART(YEAR, @datetime_counter)
AND MAX_CHECK.Day_of_Week = DATEPART(DW, @datetime_counter))
END
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 = 'Monthly, on the ' + job_summary.job_frequency_relative_interval +
CASE WHEN job_frequency_interval = 1 THEN ' Sunday' WHEN job_frequency_interval = 2 THEN ' Monday'
WHEN job_frequency_interval = 3 THEN ' Tuesday' WHEN job_frequency_interval = 4 THEN ' Wednesday'
WHEN job_frequency_interval = 5 THEN ' Thursday' WHEN job_frequency_interval = 6 THEN ' Friday'
WHEN job_frequency_interval = 7 THEN ' Saturday' END + ' of the month' +
', 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) +
' and ' + LEFT(CAST(@active_end_time AS VARCHAR(MAX)), 8) 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;
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, @job_frequency_relative_interval;
END
CLOSE job_cursor;
DEALLOCATE job_cursor;
END
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.
这些计划与以前的计划之间的主要区别是需要检查日历表以确定什么日期与计划条款匹配。 2月1 日星期二可能是2月1 日 ，也可能是7月7 日 。 用于检查此内容的CASE语句并不是很漂亮，但是会将FIRST，SECOND，THIRD，FOURTH和LAST转换为可以与任何给定日期的星期几进行比较的数字。 幸运的是，由于我们要分析的日期范围更可能是一小组天（而不是数百万天），因此此处的性能不会成为问题。
启动和CPU空闲时间表 (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.
这些是特殊的计划，出于完整性考虑，这些计划在此处包括在内，并且是可选的。 如果不需要它们，则使用@include_startup_and_idle_jobs_in_summary_data位将其过滤掉。
IF @include_startup_and_idle_jobs_in_summary_data = 1
BEGIN
IF EXISTS (SELECT * FROM #job_summary WHERE job_frequency = 'COMPUTER IDLE')
BEGIN
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,
'1/1/1901' AS job_run_time_utc,
'1/1/1901' AS job_run_time_local
FROM #job_summary job_summary
WHERE job_frequency = 'COMPUTER IDLE';
UPDATE job_summary
SET job_schedule_description = 'Runs when computer is idle.'
FROM #job_summary job_summary
WHERE job_frequency = 'COMPUTER IDLE';
END
IF EXISTS (SELECT * FROM #job_summary WHERE job_frequency = 'AGENT STARTUP')
BEGIN
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,
'1/1/1901' AS job_run_time_utc,
'1/1/1901' AS job_run_time_local
FROM #job_summary job_summary
WHERE job_frequency = 'AGENT STARTUP';
UPDATE job_summary
SET job_schedule_description = 'Runs when SQL Server Agent starts.'
FROM #job_summary job_summary
WHERE job_frequency = 'AGENT STARTUP';
END
END
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.
当我们将所有这些代码串在一起存储到存储过程中时，我们可以在任何SQL Server上运行一段时间，然后返回作业列表。 前面介绍的参数将用作存储过程参数，以便我们可以从所需的任何目标维护数据库中快速执行此参数。
Here is an execution of the final stored proc on my local server, which contains a variety of test jobs and schedules:
这是我的本地服务器上最终存储的proc的执行，其中包含各种测试作业和计划：
EXEC dbo.generate_job_schedule_data
@start_time_utc = NULL,
@end_time_utc = NULL,
@start_time_local = '1/21/2019 00:00:00',
@end_time_local = '1/21/2019 02:00:00',
@return_summarized_data = 1,
@include_startup_and_idle_jobs_in_summary_data = 0;
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.
由于我以当地时间作为参数传递的，因此结果会与那些时间协调，尽管也会出于提供信息的目的而返回UTC。 汇总结果，包括每个作业的第一次和最后一次运行时间，以及总作业数和对作业时间表的友好描述。
We can adjust the proc execution to include startup & idle CPU jobs, like this:
我们可以调整proc执行以包括启动和空闲CPU作业，如下所示：
EXEC dbo.generate_job_schedule_data
@start_time_utc = NULL,
@end_time_utc = NULL,
@start_time_local = '1/21/2019 00:00:00',
@end_time_local = '1/21/2019 02:00:00',
@return_summarized_data = 1,
@include_startup_and_idle_jobs_in_summary_data = 1;
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.
结果与前面的示例相同，只是增加了空闲CPU和启动作业的行。 请注意，每个作业的作业运行计数为零，因为在指定的时间段内无法预测执行。 尽管如此，在计划（或从维护中恢复）知道这些工作的存在仍然很有用。
Similarly, we can enter times in UTC:
同样，我们可以在UTC中输入时间：
EXEC dbo.generate_job_schedule_data
@start_time_utc = '1/21/2019 05:00:00',
@end_time_utc = '1/21/2019 07:00:00',
@start_time_local = NULL,
@end_time_local = NULL,
@return_summarized_data = 1,
@include_startup_and_idle_jobs_in_summary_data = 1;
The results will be identical to the previous example as the UTC times provided are equivalent to the local times on my server.
结果将与前面的示例相同，因为提供的UTC时间等于我服务器上的本地时间。
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:
最后，我们可以扩展作业的运行时间，以便不进行汇总。 如果我们想处理自己的指标，或者时间表运行列表足够短以至于无法手动处理，这将非常有用：
EXEC dbo.generate_job_schedule_data
@start_time_utc = '1/21/2019 05:00:00',
@end_time_utc = '1/21/2019 07:00:00',
@start_time_local = NULL,
@end_time_local = NULL,
@return_summarized_data = 0,
@include_startup_and_idle_jobs_in_summary_data = 0;
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.
在此示例中，总共返回了80行，每个不同的作业执行一个。 这可能是一个很大的数据集，因此对于大多数用例来说，汇总通常是返回此数据的理想方式。
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 规划维护并确保在SQL Server代理服务未运行时不会丢失任何作业
- 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 了解作业计划和SQL Server代理的用法
例外和自定义 (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.
能够快速绘制出给定时间段内所有预期的作业运行，可以节省大量时间。 我们可以预测未来SQL Server代理计划，更自信地计划维护，并通过更好地了解丢失的内容来应对中断。
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.
此存储过程的自然下一步就是将其结果与SQL Server代理的作业历史进行比较，以使可以检查丢失的作业并在发生这种情况时提醒操作员的过程自动化。
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)
msdb.dbo.sysschedules是较复杂的系统表之一。 以下MSDN链接对其进行了更详细的说明，这在定制此代码时可能会有所帮助： 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 |
| 使用SQL Server代理生成计划 |
| 检测和警报SQL Server代理丢失的作业 |
翻译自: https://www.sqlshack.com/generating-schedules-with-sql-server-agent/
2579
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
