Problem
Our SQL Server Agent service includes jobs with a rich set of schedules to monitor and maintain. Because we have an ongoing need to add new jobs, replace existing jobs, and modify job schedules, senior IT management and client organization staff are not always sure which jobs are scheduled to run when. Please provide some case study examples that demonstrate how to manage such a rich and dynamic job environment — particularly regarding job schedules.
Solution
It can be tedious to provide current information about SQL Server Agent job schedules especially when – there are many jobs to track, some jobs have multiple schedules, and both jobs and their schedules need to be regularly modified. This tip will introduce you to the basics of how to thrive in this kind of environment.
You will gain an introduction to managing job schedules through a review of key msdb tables pertaining to jobs and job schedules. For example, the tip will drill down on the sysjobs, sysjobschedules, and sysschedules tables. An initial set of queries will introduce you to these tables. Additionally, you’ll learn about different types of job schedules as well as how to both display and modify them. All key fields from the sysschedules table will be covered so that you can readily use them to display and manage job schedules. Finally, several code samples demonstrate how to create new schedules and attach them to jobs.
By the end of the tip, you will have reviewed code samples for:
- Selectively displaying job and schedule information from msdb database base tables
- Displaying and managing job schedules that use all key sysschedules fields
- Working with two different kinds of monthly schedules
- Creating and dropping schedules
- Attaching and detaching schedules to jobs
The SQL Server Agent service for this tip is populated by jobs from three prior tips as well as three variations of one of these jobs especially created for this tip. You will be better able to understand this tip if you scan three prior tips to learn more about job-schedule pairs from these prior tips:
- Getting Started with SQL Server Agent – Part 1
- Creating Multi-Step and Dynamic SQL Server Agent Jobs
- Scripting New SQL Agent Jobs from Prior Job Scripts
Basic SQL Server Agent Job and Schedule Queries
A good place to start listing information about SQL Server Agent jobs and their schedules is from the msdb sysjobs table. The following script lists jobs. The query lists jobs by name, description, and enabled status. The query does not return job_id column values, but the job_id uniqueidentifier field is guaranteed to be distinct across rows in the sysjobs table and other msdb tables pertaining to jobs and job schedules.
You can think of the name and description column values as short and long names, respectively, for a job. The enabled column values have a tinyint data type. Rows with an enabled value of 1 are for jobs that can be launched by an enabled schedule attached to the job. Enabled jobs are not required to have a schedule. Rows with an enabled value of 0 are for jobs that cannot be launched by a schedule even if they have an enabled schedule attached to them. If a job is enabled without an enabled schedule, you can still start it manually on demand; see the instructions for accomplishing this here.
-- list all jobs
select
name
,enabled
,description
from msdb.dbo.sysjobs
The following screen shot shows the outcome for the initial set of jobs on the SQL Agent used for this tip. Notice there are a total of twelve jobs, but two jobs are not enabled. The syspolicy_purge_history job is a built-in job that manages the sysjobhistory table. It was reported in a prior tip that the two disabled jobs conflict with the Create a two-step reporting job. The jobs did not initially conflict with each other, but they did conflict as more jobs were successively added to the SQL Agent service. You should remain vigilant for this kind of issue emerging as you add more jobs to a SQL Agent service over time.
The next script lists values for selected key columns in the msdb sysschedules table. Schedules can specify an individual day or a group of days on which jobs attached to the schedule will be started. The freq_type field identifies these kinds of schedules. Schedules can also be based on day parts, such as hours, minutes, or seconds; the freq_subday_type field denotes this kind of schedule. Both the freq_interval and freq_subday_interval denote more specific details about job schedules over days or day parts. The freq_recurrence_factor can indicate how frequently a job runs across freq_interval values. For example, a schedule with freq_recurrence_factor of 3 and a freq_interval value for a month would run every third month or once per quarter.
-- list all schedules with selected attributes
select
name
,enabled
,freq_type
,freq_interval
,freq_subday_type
,freq_subday_interval
,freq_recurrence_factor
from msdb.dbo.sysschedules
The following screen shot shows the result set for the preceding script for the initial SQL Agent schedules for this tip. There is a total of 13 schedules in the SQL Agent as of the time of this screen shot was taken. As alluded to previously, schedules – just like jobs – can have an enabled status. For this tip, all schedules on the SQL Agent service are enabled, but this status is not required.
- If a job is enabled and the schedule attached to the job is enabled, then SQL Agent will launch the job on schedule so long as the SQL Agent is started.
- If a job is not enabled and the schedule attached to the job is enabled, then SQL Agent will not launch the job on schedule even if the SQL Agent is started.
- If a job is enabled but the schedule attached to a job is not enabled, then SQL Agent will not launch the job on schedule even if the SQL Agent is started.
- If both a job is not enabled and the schedule to which a job is attached is not enabled, then SQL Agent will not launch the job on schedule even if the SQL Agent is started.
The numeric values for the sysschedules table columns are defined within the Microsoft documentation for the sysschedules table. Rather than repeat that documentation here, you are referred to it. You should consider scanning or at least referencing it as required because code samples in this tip will be using selected values for sysschedules columns to describe and create schedules.
If a job has a schedule attached to it, then the job will have its job_id value in the sysjobschedules table. Each row within the sysjobschedules table also contains a schedule_id value, which is the primary key for the sysschedules table. Therefore, you can enumerate all jobs with a schedule via an inner join between the sysjobs and sysjobschedules tables by job_id. The following script shows how to accomplish this. The order by clause in the script arranges the jobs so that enabled jobs appear before jobs that are not enabled.
-- list jobs with an attached schedule
select
name
,enabled
,description
from msdb.dbo.sysjobs
inner join msdb.dbo.sysjobschedules on sysjobs.job_id = sysjobschedules.job_id
order by enabled desc
The next screen shot displays the results from the preceding query for the SQL Agent jobs in this tip. Notice that just seven jobs are in the result – meaning that the remaining five jobs in the SQL Agent do not have a schedule associated with them. Additionally, two of the seven jobs are not enabled; these jobs are denoted with an enabled value of 0. Therefore, the two jobs that are not enabled can only be started on demand.
The description field value for the Four step job with error branching job has its value truncated because the text for the description is too long to fit in the allotted space. If you wish to see the full description or learn more about the job you can follow this link.
For your easy reference, the following script enumerates jobs without a schedule. Additionally, the result set from the script appears immediately below it. All five of these jobs run only on demand because they have no schedule attached to them.
-- list all jobs without a schedule
select
name
,enabled
,description
from msdb.dbo.sysjobs where job_id in
(
-- job_ids without a schedule
select job_id from msdb.dbo.sysjobs
except
select job_id from msdb.dbo.sysjobschedules
)
The next script shows a join of the sysjobs table with the sysschedules table through the sysjobschedules table. The result set, which appears immediately below the script, includes identifiers from both the sysjobs and sysschedules tables. Recall that the job_id value from the sysjobs table uniquely identifies each job within a SQL Agent service. The schedule_id value within the sysschedules table is the primary key for the schedules denoted by the rows of the sysschedules table. The schedule_uid is a uniqueidentifier data type value that serves as a candidate key in the sysschedules table.
Notice from the result set that there are two jobs that are not enabled, but they have schedules attached to them. The attached schedules have the name Daily at midnight. The Create a two-step reporting job also has a schedule named Daily at midnight, but this job is enabled. Consequently, the only job with a schedule name of Daily at midnight that launches automatically on schedule is the Create a two-step reporting job.
-- jobs that have a schedule with schedule identifiers
select
sysjobs.job_id
,sysjobs.name job_name
,sysjobs.enabled job_enabled
,sysschedules.name schedule_name
,sysschedules.schedule_id
,sysschedules.schedule_uid
,sysschedules.enabled schedule_enabled
from msdb.dbo.sysjobs
inner join msdb.dbo.sysjobschedules on sysjobs.job_id = sysjobschedules.job_id
inner join msdb.dbo.sysschedules on sysjobschedules.schedule_id = sysschedules.schedule_id
order by sysjobs.enabled desc
Displaying SQL Server Agent Jobs with Daily and Weekly Schedules
All the initial jobs in the SQL Server Agent for this tip have daily or weekly schedules. The procedure for displaying schedule information differs slightly depending on whether a job has a daily schedule or a weekly schedule. The term daily schedule is used to denote a schedule that can launch a job every day. The term weekly schedule denotes a schedule that can launch a job on some subset, including all, days of the week. In order to extract which subset of days a weekly schedule starts jobs, we need different code than for a daily schedule.
The following script extracts information about jobs separately for daily and weekly jobs. Then, the result sets of the two separate queries are concatenated with a union operator into one result set. Separate comment lines mark the beginning of the code block for extracting daily and weekly job schedule information. An order by clause for the overall result set arranges the rows so that jobs which are enabled appear before jobs that are not enabled.
The script specifies seven columns for the combined result set.
- The first column is for the name of a job; the values for this column are derived from the sysjobs table.
- The second column has the name job_enabled; the field values for this second column are also from the sysjobs table.
- The third column has the name schedule_name; this field value, along with all the remaining field values in the overall result set, is from the sysschedules table.
- The fourth column has the name freq_recurrence_factor. Recall that this term designates the number of freq_intervals that elapse between starts of jobs attached to the schedule. If the freq_recurrence_factor is 1 for a weekly schedule, it means the job runs each week. If the freq_recurrence_factor value is 0, it means that the freq_recurrence_factor does not apply to the attached job schedule.
- The fifth column has the name frequency. The value for this column derives from freq_type in the sysschedules table. Schedules with a freq_type value of 4 have daily schedules and schedules with a freq_type value of 8 have a weekly schedule.
- The sixth column has the name Days. The values for this column are computed differently for daily and weekly schedules.
- For daily schedules, the days on which this type of schedule runs is every freq_interval days. All the daily schedules displayed in the screen shot below have a freq_interval of 1, which means the daily schedules run on every day although they are different in other details each.
- For weekly schedules, the days on which a schedule runs can be any subset of the seven days in a week. The script below uses the & bitwise operator to determine which days are job start days in the schedule.
- The seventh column has the name time. This column value can be computed on up to two sysschedules column values.
- If the freq_subday_type value is 2, 4, or 8, then the schedule repeats, respectively, on a second, minute, or hourly basis for every freq_subday_interval value of a schedule starting at a designated time.
- If the freq_subday_type value is 1, the schedule runs once starting at a designated time.
- The designated start time for a daily or weekly schedule is based on the active_start_time value from the sysschedules table.
The Microsoft documentation for the sysschedules table assigns values of 1, 2, 4, 8, 16, 32, and 64 progressively for the days within a weekly schedule from Sunday (1), Monday (2), Tuesday (4) through Saturday (64). The & bitwise operator compares the 8-bit byte pattern for a day to the 8-bit byte pattern for the value denoting the days on which a schedule may run. For example, if a schedule may run on Sunday and Saturday, its byte pattern is 10000010, which corresponds to the decimal value of 65. The Sunday & bitwise comparison would be for 10000000 versus 10000010, which returns a value of 10000000 for Sunday, and the Saturday comparison would be for 00000010 versus 10000010, which returns a value of 00000010 for Saturday. The 8-bit byte pattern for a schedule that launches jobs on Monday, Tuesday, Wednesday, Thursday, Friday, and Saturday is 01111110, which equals a decimal value of 126. A succession of & operators in the code below deciphers start day byte patterns into the sequence of days for the Days field value in the result set.
-- list jobs and schedule info with daily and weekly schedules
-- jobs with a daily schedule
select
sysjobs.name job_name
,sysjobs.enabled job_enabled
,sysschedules.name schedule_name
,sysschedules.freq_recurrence_factor
,case
when freq_type = 4 then 'Daily'
end frequency
,
'every ' + cast (freq_interval as varchar(3)) + ' day(s)' Days
,
case
when freq_subday_type = 2 then ' every ' + cast(freq_subday_interval as varchar(7))
+ ' seconds' + ' starting at '
+ stuff(stuff(RIGHT(replicate('0', 6) + cast(active_start_time as varchar(6)), 6), 3, 0, ':'), 6, 0, ':')
when freq_subday_type = 4 then ' every ' + cast(freq_subday_interval as varchar(7))
+ ' minutes' + ' starting at '
+ stuff(stuff(RIGHT(replicate('0', 6) + cast(active_start_time as varchar(6)), 6), 3, 0, ':'), 6, 0, ':')
when freq_subday_type = 8 then ' every ' + cast(freq_subday_interval as varchar(7))
+ ' hours' + ' starting at '
+ stuff(stuff(RIGHT(replicate('0', 6) + cast(active_start_time as varchar(6)), 6), 3, 0, ':'), 6, 0, ':')
else ' starting at '
+stuff(stuff(RIGHT(replicate('0', 6) + cast(active_start_time as varchar(6)), 6), 3, 0, ':'), 6, 0, ':')
end time
from msdb.dbo.sysjobs
inner join msdb.dbo.sysjobschedules on sysjobs.job_id = sysjobschedules.job_id
inner join msdb.dbo.sysschedules on sysjobschedules.schedule_id = sysschedules.schedule_id
where freq_type = 4
union
-- jobs with a weekly schedule
select
sysjobs.name job_name
,sysjobs.enabled job_enabled
,sysschedules.name schedule_name
,sysschedules.freq_recurrence_factor
,case
when freq_type = 8 then 'Weekly'
end frequency
,
replace
(
CASE WHEN freq_interval&1 = 1 THEN 'Sunday, ' ELSE '' END
+CASE WHEN freq_interval&2 = 2 THEN 'Monday, ' ELSE '' END
+CASE WHEN freq_interval&4 = 4 THEN 'Tuesday, ' ELSE '' END
+CASE WHEN freq_interval&8 = 8 THEN 'Wednesday, ' ELSE '' END
+CASE WHEN freq_interval&16 = 16 THEN 'Thursday, ' ELSE '' END
+CASE WHEN freq_interval&32 = 32 THEN 'Friday, ' ELSE '' END
+CASE WHEN freq_interval&64 = 64 THEN 'Saturday, ' ELSE '' END
,', '
,''
) Days
,
case
when freq_subday_type = 2 then ' every ' + cast(freq_subday_interval as varchar(7))
+ ' seconds' + ' starting at '
+ stuff(stuff(RIGHT(replicate('0', 6) + cast(active_start_time as varchar(6)), 6), 3, 0, ':'), 6, 0, ':')
when freq_subday_type = 4 then ' every ' + cast(freq_subday_interval as varchar(7))
+ ' minutes' + ' starting at '
+ stuff(stuff(RIGHT(replicate('0', 6) + cast(active_start_time as varchar(6)), 6), 3, 0, ':'), 6, 0, ':')
when freq_subday_type = 8 then ' every ' + cast(freq_subday_interval as varchar(7))
+ ' hours' + ' starting at '
+ stuff(stuff(RIGHT(replicate('0', 6) + cast(active_start_time as varchar(6)), 6), 3, 0, ':'), 6, 0, ':')
else ' starting at '
+ stuff(stuff(RIGHT(replicate('0', 6) + cast(active_start_time as varchar(6)), 6), 3, 0, ':'), 6, 0, ':')
end time
from msdb.dbo.sysjobs
inner join msdb.dbo.sysjobschedules on sysjobs.job_id = sysjobschedules.job_id
inner join msdb.dbo.sysschedules on sysjobschedules.schedule_id = sysschedules.schedule_id
where freq_type = 8
order by job_enabled desc
The following screen shot displays the outcome of the preceding script for the initial set of jobs and schedules in the SQL Agent for this tip. Notice particularly the frequency and time column values which show on rows 2 and 3. Although both schedules have the same freq_subday_interval of 5, they have different freq_type and active_start_time values.
- The schedule on the second row (Run on selected days of the week at a specified time) has a weekly frequency corresponding to a freq_type value of 8. The schedule on the third row has a freq_type value of 4 which corresponds to a daily frequency.
- The active_start_time value for the second row is 3 PM (150000), but the active_start_time value for the third row is midnight (000000). The active_start_time values in the sysschedules table are re-formatted to values that look like time values for display.
Displaying SQL Server Agent Jobs with Monthly Schedules
SQL Agent supports two types of monthly schedules.
- One type of monthly schedule denotes the numerical day number of the month on which a schedule runs. This type of schedule has a freq_type value of 16 (unlike the values of 4 for jobs running on a daily basis and 8 for jobs running on a weekly basis). The freq_type_interval value denotes the specific day of the month on which a job runs.
- A second type of monthly schedule reflects relative day(s) within a month on which a schedule runs.
- This type of schedule has a freq_type value of 32. The freq_type_interval values for this second type of monthly schedule are
- 1 for Sunday
- 2 for Monday
- 3 for Tuesday
- 4 for Wednesday
- 5 for Thursday
- 6 for Friday
- 7 for Saturday
- 8 for Day
- 9 for Weekday
- 10 for Weekend day
- A second sysschedules column value (freq_relative_interval) modulates how to interpret the freq_type_interval values for schedules with a freq_type value of 32. The freq_relative_interval values are
- 1 for First
- 2 for Second
- 4 for Third
- 8 for Fourth
- 16 for Last
- For example, to denote a schedule that runs the last Wednesday of every month, use the following settings:
- freq_type = 32
- freq_type_interval = 4
- freq_relative_interval = 16
- This type of schedule has a freq_type value of 32. The freq_type_interval values for this second type of monthly schedule are
Because the initial set of jobs in our SQL Agent does not include any jobs with a monthly schedule, we demonstrate how to create two new job-schedule pairs – one for a monthly schedule with a freq_type value of 16 and another for a monthly schedule with a freq_type value of 32. The following script shows how to accomplish this for the Insert into JobRunLog table with a schedule job with an initial schedule named Weekly on Saturday Morning at 1 AM. The script is distinct from another perspective as well in that it assigns two different schedules to the same job. Up until this point in the tip, each SQL Agent job had 0 or 1 schedules attached to it, but SQL Agent permits jobs to have two or more schedules attached to them.
The following script includes three separate T-SQL batches.
- The first batch detaches the initial schedule (Weekly on Saturday Morning at 1 AM) from the Insert into JobRunLog table with a schedule job.
- The second batch creates a new schedule named Run last second of last day each month before attaching it to the Insert into JobRunLog table with a schedule job.
- The third batch creates a second schedule (Run last second of 15th day of the month) and attaches it to the Insert into JobRunLog table with a schedule job.
Detaching the initial schedule from the job involves invoking the sp_detach_schedule stored procedure from the msdb database. This stored procedure takes two parameters. The first parameter, @job_name, denotes the name of the job from which to detach a schedule. The second parameter, @schedule_name, references the name of the schedule to detach.
The process for attaching a new schedule to the Insert into JobRunLog table with a schedule job is essentially the same for both new schedules.
- First, you make sure that no current schedule exists with the name of the schedule that you want to attach to the job.
- Second, you invoke the sp_add_schedule stored procedure with the parameters for your new schedule. You need to specify an active_start_time (235958) that is at least one second before the active_end_time (235959) for the schedule, which is the last second of the day.
- Third, you invoke the sp_attach_schedule stored procedure to attach the schedule created with the sp_add_schedule stored procedure to the job.
The GO between the second and third batches is required to allow each schedule to be attached separately to the Insert into JobRunLog table with a schedule job.
-- run code to assign two monthly schedules to one job
-- detach Weekly on Saturday Morning at 1 AM schedule
-- for Insert into JobRunLog table with a schedule job
exec msdb.dbo.sp_detach_schedule
@job_name = 'Insert into JobRunLog table with a schedule',
@schedule_name = 'Weekly on Saturday Morning at 1 AM' ;
GO
-- add a schedule to run last second of last day of each month
-- and attach it to the Insert into JobRunLog table with a schedule job
declare @ReturnCode int
if exists (select name from msdb.dbo.sysschedules WHERE name = N'Run last second of last day each month')
delete from msdb.dbo.sysschedules where name=N'Run last second of last day each month'
exec @ReturnCode = msdb.dbo.sp_add_schedule
@schedule_name = N'Run last second of last day each month',
@enabled=1,
@freq_type=32, -- means monthly relative
@freq_interval=8, -- means day for monthly relative
@freq_subday_type=1,
@freq_subday_interval=0,
@freq_relative_interval=16, -- means last for freq_interval with monthly relative
@freq_recurrence_factor=1,
@active_start_date=20170809,
@active_end_date=99991231,
@active_start_time=235958, -- must schedule at least 1 second before last second of day
@active_end_time=235959
exec @ReturnCode = msdb.dbo.sp_attach_schedule
@job_name = N'Insert into JobRunLog table with a schedule',
@schedule_name = N'Run last second of last day each month'
GO
-- add a schedule to run last second of 15th day of each month
-- and also attach it to the Insert into JobRunLog table with a schedule job
declare @ReturnCode int
if exists (select name from msdb.dbo.sysschedules WHERE name=N'Run last second of 15th day of the month')
delete from msdb.dbo.sysschedules where name=N'Run last second of 15th day of the month'
exec @ReturnCode = msdb.dbo.sp_add_schedule
@schedule_name = N'Run last second of 15th day of the month',
@enabled=1,
@freq_type=16, -- means monthly
@freq_interval=15, -- means 15 day of month
@freq_subday_type=1,
@freq_subday_interval=0,
@freq_relative_interval=0,
@freq_recurrence_factor=1,
@active_start_date=20170809,
@active_end_date=99991231,
@active_start_time=235958, -- must schedule at least 1 second before last second of day
@active_end_time=235959
exec @ReturnCode = msdb.dbo.sp_attach_schedule
@job_name = N'Insert into JobRunLog table with a schedule',
@schedule_name = N'Run last second of 15th day of the month'
The next script includes the code for displaying seven columns of descriptive information about the job-schedule pairs just added to the SQL Agent service. These job-schedule pairs include an initial one with a freq_type value of 32 and a second one with a freq_type value of 16. Because these are the only two monthly schedules on the SQL Agent, the filter for schedules with a freq_type_value of 16 or 32 displays information about just the two schedules added in the preceding script.
-- jobs with a monthly schedule
select
sysjobs.name job_name
,sysjobs.enabled job_enabled
,sysschedules.name schedule_name
,sysschedules.freq_recurrence_factor
,case
when freq_type = 4 then 'Daily'
when freq_type = 8 then 'Weekly'
when freq_type = 16 then 'Monthly'
when freq_type = 32 then 'Monthly'
end frequency
,
case
when freq_type = 32 then
(
case
when freq_relative_interval = 1 then 'First '
when freq_relative_interval = 2 then 'Second '
when freq_relative_interval = 4 then 'Third '
when freq_relative_interval = 8 then 'Fourth '
when freq_relative_interval = 16 then 'Last '
end
+
replace
(
case when freq_interval = 1 THEN 'Sunday, ' ELSE '' END
+case when freq_interval = 2 THEN 'Monday, ' ELSE '' END
+case when freq_interval = 3 THEN 'Tuesday, ' ELSE '' END
+case when freq_interval = 4 THEN 'Wednesday, ' ELSE '' END
+case when freq_interval = 5 THEN 'Thursday, ' ELSE '' END
+case when freq_interval = 6 THEN 'Friday, ' ELSE '' END
+case when freq_interval = 7 THEN 'Saturday, ' ELSE '' END
+case when freq_interval = 8 THEN 'Day of Month, ' ELSE '' END
+case when freq_interval = 9 THEN 'Weekday, ' ELSE '' END
+case when freq_interval = 10 THEN 'Weekend day, ' ELSE '' END
,', '
,''
)
)
else cast(freq_interval as varchar(3)) END Days
,
case
when freq_subday_type = 2 then ' every ' + cast(freq_subday_interval as varchar(7))
+ ' seconds' + ' starting at '
+ stuff(stuff(RIGHT(replicate('0', 6) + cast(active_start_time as varchar(6)), 6), 3, 0, ':'), 6, 0, ':')
when freq_subday_type = 4 then ' every ' + cast(freq_subday_interval as varchar(7))
+ ' minutes' + ' starting at '
+ stuff(stuff(RIGHT(replicate('0', 6) + cast(active_start_time as varchar(6)), 6), 3, 0, ':'), 6, 0, ':')
when freq_subday_type = 8 then ' every ' + cast(freq_subday_interval as varchar(7))
+ ' hours' + ' starting at '
+ stuff(stuff(RIGHT(replicate('0', 6) + cast(active_start_time as varchar(6)), 6), 3, 0, ':'), 6, 0, ':')
else ' starting at '
+ stuff(stuff(RIGHT(replicate('0', 6) + cast(active_start_time as varchar(6)), 6), 3, 0, ':'), 6, 0, ':')
end time
from msdb.dbo.sysjobs
inner join msdb.dbo.sysjobschedules on sysjobs.job_id = sysjobschedules.job_id
inner join msdb.dbo.sysschedules on sysjobschedules.schedule_id = sysschedules.schedule_id
where freq_type in (16, 32)
The following screen shot shows the outcome from the preceding script. Notice that there are just two rows – one for each of the two new job-schedule pairs. One schedule runs on the last day of the month, and the other schedule runs on the fifteenth day of the month. Both schedules start at 23:59:58 on the days that they run.
This part of the code sample for creating and displaying monthly schedules is concluded by restoring the initial schedule (Weekly on Saturday Morning at 1 AM) for the Insert into JobRunLog table with a schedule job. The following script removes the two new job-schedule pairs and restores the initial schedule for the Insert into JobRunLog table with a schedule job. When you are removing a schedule, it is necessary to remove the schedule reference from the sysjobschedules table before removing it from the sysschedules table. After both new job-schedule pairs are removed from the SQL Agent by the script, the code re-attaches the Weekly on Saturday Morning at 1 AM schedule to the Insert into JobRunLog table with a schedule job.
-- remove monthly schedules and restore original
-- schedule to the Insert into JobRunLog table with a schedule job
delete from msdb.dbo.sysjobschedules where schedule_id in
(
select schedule_id
from msdb.dbo.sysschedules
where name in ('Run last second of last day each month', 'Run last second of 15th day of the month')
)
delete from msdb.dbo.sysschedules where schedule_id in
(
select schedule_id
from msdb.dbo.sysschedules
where name in ('Run last second of last day each month', 'Run last second of 15th day of the month')
)
exec msdb.dbo.sp_attach_schedule
@job_name = 'Insert into JobRunLog table with a schedule',
@schedule_name = 'Weekly on Saturday Morning at 1 AM';
Demonstrating the use of the freq_recurrence_factor Values within Schedules
All the schedules to this point in the tip have freq_recurrence_factor values of 0 or 1. The freq_recurrence_factor is always 0 unless the freq_type is 8, 16, or 32. Recall that a freq_type value of 8 is for a weekly schedule, and freq_type values of 16 and 32 are for monthly schedules. Freq_recurrence_factor values of 1 mean a schedule starts every 1 week or month depending on the value of freq_type. Freq_recurrence_factor values of greater than 1 indicate that job starts are extended for more than one week or month. For example, a schedule with a freq_type of 8 and a freq_recurrence_factor of 2 starts any attached jobs every other week (the jobs start on a bi-weekly basis).
The following script creates and attaches a bi-weekly schedule to the Insert into JobRunLog table with a schedule job. As with the preceding example for monthly schedules, the script starts by detaching the Weekly on Saturday Morning at 1 AM schedule from the Insert into JobRunLog table with a schedule job. Next, the script creates the new bi-weekly schedule using a freq_recurrence_factor value of 2. Then, the new bi-weekly schedule is attached to the Insert into JobRunLog table with a schedule job.
-- run code to assign a bi-weekly schedule to one job
-- detach Weekly on Saturday Morning at 1 AM schedule
-- for Insert into JobRunLog table with a schedule job
exec msdb.dbo.sp_detach_schedule
@job_name = 'Insert into JobRunLog table with a schedule',
@schedule_name = 'Weekly on Saturday Morning at 1 AM' ;
GO
-- add a schedule to run bi-weekly on Saturday morning at 1 AM
-- and attach it to the Insert into JobRunLog table with a schedule job
declare @ReturnCode int
if exists (select name from msdb.dbo.sysschedules WHERE name = N'Bi-weekly on Saturday Morning at 1 AM')
delete from msdb.dbo.sysschedules where name=N'Bi-weekly on Saturday Morning at 1 AM'
exec @ReturnCode = msdb.dbo.sp_add_schedule
@schedule_name = N'Bi-weekly on Saturday Morning at 1 AM',
@enabled=1,
@freq_type=8, -- means job runs on a weekly basis
@freq_interval=64, -- means job runs on Saturday within a week
@freq_subday_type=1,
@freq_subday_interval=0,
@freq_relative_interval=0,
@freq_recurrence_factor=2, -- means job runs every other freq_type
@active_start_date=20170729,
@active_end_date=99991231,
@active_start_time=10000, -- means job runs at 1 AM in day
@active_end_time=235959
exec @ReturnCode = msdb.dbo.sp_attach_schedule
@job_name = N'Insert into JobRunLog table with a schedule',
@schedule_name = N'Bi-weekly on Saturday Morning at 1 AM'
The next script displays all attached weekly schedules for the Insert into JobRunLog table with a schedule job. The following screen shot shows the result set for the query. Notice that the screen shot shows the Bi-weekly on Saturday Morning at 1 AM schedule attached to the Insert into JobRunLog table with a schedule job. The schedule is bi-weekly because the freq_recurrence_factor is 2 for the Bi-weekly on Saturday Morning at 1 AM schedule.
-- jobs with a weekly schedule where job name is
-- Insert into JobRunLog table with a schedule
select
sysjobs.name job_name
,sysjobs.enabled job_enabled
,sysschedules.name schedule_name
,sysschedules.freq_recurrence_factor
,case
when freq_type = 8 then 'Weekly'
end frequency
,
replace
(
CASE WHEN freq_interval&1 = 1 THEN 'Sunday, ' ELSE '' END
+CASE WHEN freq_interval&2 = 2 THEN 'Monday, ' ELSE '' END
+CASE WHEN freq_interval&4 = 4 THEN 'Tuesday, ' ELSE '' END
+CASE WHEN freq_interval&8 = 8 THEN 'Wednesday, ' ELSE '' END
+CASE WHEN freq_interval&16 = 16 THEN 'Thursday, ' ELSE '' END
+CASE WHEN freq_interval&32 = 32 THEN 'Friday, ' ELSE '' END
+CASE WHEN freq_interval&64 = 64 THEN 'Saturday, ' ELSE '' END
,', '
,''
) Days
,
case
when freq_subday_type = 2 then ' every ' + cast(freq_subday_interval as varchar(7))
+ ' seconds' + ' starting at '
+ stuff(stuff(RIGHT(replicate('0', 6) + cast(active_start_time as varchar(6)), 6), 3, 0, ':'), 6, 0, ':')
when freq_subday_type = 4 then ' every ' + cast(freq_subday_interval as varchar(7))
+ ' minutes' + ' starting at '
+ stuff(stuff(RIGHT(replicate('0', 6) + cast(active_start_time as varchar(6)), 6), 3, 0, ':'), 6, 0, ':')
when freq_subday_type = 8 then ' every ' + cast(freq_subday_interval as varchar(7))
+ ' hours' + ' starting at '
+ stuff(stuff(RIGHT(replicate('0', 6) + cast(active_start_time as varchar(6)), 6), 3, 0, ':'), 6, 0, ':')
else ' starting at '
+ stuff(stuff(RIGHT(replicate('0', 6) + cast(active_start_time as varchar(6)), 6), 3, 0, ':'), 6, 0, ':')
end time
from msdb.dbo.sysjobs
inner join msdb.dbo.sysjobschedules on sysjobs.job_id = sysjobschedules.job_id
inner join msdb.dbo.sysschedules on sysjobschedules.schedule_id = sysschedules.schedule_id
where freq_type = 8 and sysjobs.name = 'Insert into JobRunLog table with a schedule'
The closing script for this tip restores the original schedule to the Insert into JobRunLog table with a schedule job.
-- remove bi-weekly schedule and restore original
-- schedule to the Insert into JobRunLog table with a schedule job
delete from msdb.dbo.sysjobschedules where schedule_id in
(
select schedule_id
from msdb.dbo.sysschedules
where name in ('Bi-weekly on Saturday Morning at 1 AM')
)
delete from msdb.dbo.sysschedules where schedule_id in
(
select schedule_id
from msdb.dbo.sysschedules
where name in ('Bi-weekly on Saturday Morning at 1 AM')
)
exec msdb.dbo.sp_attach_schedule
@job_name = 'Insert into JobRunLog table with a schedule',
@schedule_name = 'Weekly on Saturday Morning at 1 AM';
Next Steps
- Use the queries in the basic job and schedule queries from msdb tables section to explore the jobs and schedules on your SQL Agent service. By deciphering the values in the result sets, you will grow your understanding of how SQL Agent uses column values in the sysjobs, sysjobschedules, and sysschedules msdb tables to keep track of jobs and their schedules.
- Next, use the query in the Displaying jobs and schedule info with daily and weekly schedules section to display summaries of all the daily and weekly job-schedule pairs on your SQL Agent. This query and the queries from the preceding section do not change anything on a SQL Agent service.
- Then, excerpt the code for just displaying job-schedule pairs from the Displaying jobs and schedule info with monthly schedules section. The excerpted code will display all jobs with monthly schedules on your SQL Agent.
- The next time you need to add a new schedule and attach it to an existing job consider using the code for this purpose as a model from the displaying jobs and schedule info with monthly schedules and demonstrating the use of the freq_recurrence_factor values within schedules sections.
- By digging into the code samples in this tip, you can develop rich SQL Server Agent Job schedule applications as well as get started creating your own custom code library for creating schedules, attaching schedules, and detaching schedules.
- Finally, remember to take advantage of the MSSQLTips.com treasure trove of tips on SQL Server Agent. This link presents an index for prior SQL Server Agent tips.
- Download all of the scripts.
