By: Svetlana Golovko | Updated: 2022-09-20 | Comments | Related: > SQL Server Agent
Problem
SQL Server Agent allows us to create automated jobs and schedule them to run at different repeating time intervals, run them once, run them after SQL Server Agent startup, or on a specific performance condition (Idle CPU).
After troubleshooting unusual backup space utilization, we found that one of our backup jobs in the Development environment started running twice a day. After reviewing the job's schedules, we discovered that the job now had two enabled schedules instead of one, and the second one was recently re-enabled.
We reviewed other schedules and found that some are disabled and others are unused.
How do we manage schedules properly to ensure that we do not end up with dozens of unused or disabled schedules? How do we make sure that we use and update the correct schedules? What should we consider before we delete or disable an existing schedule?
Solution
You can create a schedule that is initially not used by any jobs and then attach it to a job (or several jobs). Or you can create a schedule during a job creation using SQL Server Management Studio (SSMS). You can also use multiple schedules with a job if more flexibility is required:
Additionally, you can share a schedule among multiple jobs. Check out this other tip about shared schedules: Understanding how SQL Server Agent Jobs can share schedules.
Manage the Schedules
The schedule information is saved in the sysschedules system table in the msdb database.
If we query this table on a somewhat newly installed SQL Server without any new jobs, we will see these eight schedules:
SELECT schedule_id, name, date_created FROM msdb.dbo.sysschedules
Note: The first seven schedules were created right after the msdb database was created:
SELECT name, create_date FROM master.sys.databases WHERE [name] ='msdb'
Be aware that these are Microsoft-created schedules. It's probably a good idea to keep them as is.
Schedules-to-jobs mapping information is saved in the sysjobschedules table. Interestingly, the sysjobschedules table is refreshed every 20 minutes, meaning if you changed the job's schedule (attached/detached to/from a job), you may not see the changes immediately.
You can manage a schedule using T-SQL or SSMS.
Here are some schedules' properties that you can manage using SSMS:
Schedules Related Issues Examples
Some of the problems that we can encounter with schedules include:
Example 1: Disabling/Enabling/Updating a Schedule Used by Multiple Jobs and Inadvertently Impacting Other Jobs
This can happen when you update a schedule directly from the job's properties and do not check other "Jobs in Schedule":
Example 2: Misleading or Duplicate Schedules Names May Lead to the Incorrect Schedule Selection
The screenshot below shows two different schedules with the same name. If we choose the wrong one and update it, we may impact other jobs that use this schedule.
Another example below is the "Hourly Monitoring" schedule. The description notes that it runs every 30 minutes, not 60 minutes. It was potentially updated to run every 30 minutes; however, the schedule name was not changed.
Check out this tip that has useful queries to review the jobs and schedules: SQL Server Agent Job Schedule Reporting.
Queries to Identify Schedule-Related Potential Issues
Please note that the queries provided below are to identify some potential issues, not all of them. There might be other combinations of different schedules used together.
For instance, we do not include in this list examples of schedules that run after SQL Server Agent startup or start on "CPU-idle" performance condition. You can update the queries below to add these types of schedules if needed.
Example 1: Jobs Without Schedules
SELECT j.[name] AS job_name,
s.schedule_id ,
MAX(h.run_date) AS last_ran_hist,
MAX(v.last_run_date) AS last_ran,
a.[name] as used_in_alert
FROM msdb.dbo.sysjobs j
LEFT JOIN msdb.dbo.sysjobschedules s
ON j.job_id = s.job_id
LEFT JOIN msdb.dbo.sysjobhistory h
ON j.job_id= h.job_id
LEFT JOIN msdb.dbo.sysjobservers v
ON j.job_id= v.job_id
LEFT JOIN msdb.dbo.sysalerts a
ON j.job_id= a.job_id
WHERE s.job_id IS NULL
GROUP BY j.[name], s.schedule_id, a.[name]
Remember that some jobs might not have schedules, but other jobs can start them. Or jobs can be triggered by an alert, CPU condition, etc.
Note: In our query, we use two different dates to validate the job's last run date: one from the sysjobhistory table and the other from the sysjobservers table. This is to demonstrate a potential difference between these two values.
The highlighted value on the screenshot doesn't have a record since our Demo SQL Server Agent doesn't keep history long enough to see the last date this job ran from the sysjobhistory table.
Note: Both "last run" dates will be reset if you drop and recreate the job.
Example 2: Jobs with Multiple Schedules (to validate that this is intended)
;WITH ns AS
(SELECT j.job_id,COUNT(sh.schedule_id) AS count_schedules
FROM msdb.dbo.sysjobs j LEFT JOIN msdb.dbo.sysjobschedules sh
ON j.job_id = sh.job_id
GROUP BY j.job_id
HAVING COUNT(sh.schedule_id) > 1
)
SELECT j.[name],sh.schedule_id, sh.[name], sh.[enabled]
FROM ns JOIN msdb.dbo.sysjobs j
ON ns.job_id = j.job_id
JOIN msdb.dbo.sysjobschedules s
ON s.job_id = j.job_id
JOIN msdb.dbo.sysschedules sh
ON s.schedule_id=sh.schedule_id
Example 3: Multiple Schedules with the Same Name
This query is helpful to avoid confusion during schedule updates or selection. You may need to identify schedules with the same names, so you disable/delete the right schedule.
SELECT [name], COUNT([name]) no_of_duplicate_names FROM msdb.dbo.sysschedules GROUP BY [name] HAVING COUNT([name]) > 1
Note: The schedules with the "SSISDB Scheduler" name are created by SQL Server when you provision SQL Server Integration Services (SSIS) database.
Note: Also, schedules are allowed to have the same names. The schedule ID identifies them.
Example 4: Jobs with Disabled Schedules
This query shows jobs with disabled schedules:
SELECT j.[name] AS job_name,
s.schedule_id,
sh.[name] AS schedule_name,
sh.[enabled],
sh.freq_type,
MAX(h.run_date) AS last_ran_hist,
MAX(v.last_run_date) AS last_ran,
sh.active_start_date,
CASE WHEN sh.active_start_date >
CAST(REPLACE(CAST(CAST(GETDATE() AS DATE) AS CHAR(10)),'-','') AS INT)
THEN '!!! A Schedule''s Active Start date is in the future'
WHEN sh.[enabled] = 0 AND sh.freq_type =1 AND sh.active_start_date < MAX(v.last_run_date)
THEN 'Disabled as "Run Once" schedule (already executed)'
ELSE 'Disabled Schedule' END
FROM msdb.dbo.sysjobs j
JOIN msdb.dbo.sysjobschedules s
ON j.job_id = s.job_id
JOIN msdb.dbo.sysschedules sh
ON s.schedule_id=sh.schedule_id
LEFT JOIN msdb.dbo.sysjobhistory h
ON j.job_id= h.job_id
LEFT JOIN msdb.dbo.sysjobservers v
ON j.job_id= v.job_id
WHERE sh.[enabled] = 0
GROUP BY j.[name], s.schedule_id, sh.[name], sh.[enabled], sh.active_start_date, sh.freq_type
The last column shows comments about possible scenarios when the schedules might be disabled. Note: This example on the screenshot below has just a few scenarios noted. You may still need to review the jobs and/or schedules.
Here are some cases when a schedule becomes disabled:
- The schedule was disabled manually.
- The schedule was set up to run a job once at a specific time, and after the job was executed, the schedule became disabled.
- The schedule becomes disabled even if you enable it. This happens when you configure the schedule's active start date in the future and the next run occurs after a specific period of time. This case looks almost like a bug.
Let's dig deeper into the issue with a future run date combined with a future active start date. If today is any date before September 3rd and a schedule is created to run a job on every first day of the month, and with an active start date later than the first potential run (September 3rd), then the schedule becomes disabled as soon as you attach it to a job. Here is a step-by-step view:
Step 1: Create a test schedule using T-SQL with the active start date in the future. Note: The schedule at this point is not attached to any jobs.
DECLARE @schedule_id int
EXEC msdb.dbo.sp_add_schedule @schedule_name=N'Active Start Date Test',
@enabled=1,
@freq_type=32,
@freq_interval=8,
@freq_subday_type=1,
@freq_subday_interval=0,
@freq_relative_interval=4,
@freq_recurrence_factor=1,
@active_start_date=20220904,
@active_end_date=99991231,
@active_start_time=0,
@active_end_time=235959, @schedule_id = @schedule_id OUTPUT
select @schedule_id
GO
Step 2: Verify that the schedule is enabled.
SELECT [name], [enabled] FROM msdb.dbo.sysschedules WHERE [name] = N'Active Start Date Test'
Step 3: Attach the schedule to a job with SSMS or T-SQL.
EXEC msdb.dbo.sp_attach_schedule @job_name = N'SampleJob - 2',
@schedule_name = N'Active Start Date Test'
The schedule is disabled now.
SELECT [name], [enabled] FROM msdb.dbo.sysschedules WHERE [name] = N'Active Start Date Test'
Be aware of this case as you may expect the job to run next month, but it won't run…
Example 5: Schedules that Are Not Used
SELECT sh.[name], sh.[enabled]
FROM msdb.dbo.sysschedules sh
LEFT JOIN msdb.dbo.sysjobschedules s
ON s.schedule_id=sh.schedule_id
WHERE s.job_id IS NULL
Note: Most of the schedules on the screenshot above are Microsoft-created schedules, and we don't normally want to delete them. Also, if the schedule is deleted from the job using SSMS and the schedule is not used by any other jobs, this schedule will be permanently deleted.
So, if you want to remove the schedule from the job, but keep the schedule for any reason, use the sp_detach_schedule stored procedure.
EXEC msdb.dbo.sp_detach_schedule @job_name = N'SampleJob - 2',
@schedule_name = N'One time - remove schedule'
GO
You can also delete a schedule that is not used by other jobs during the job's deletion (using @delete_unused_schedule parameter):
EXEC msdb.dbo.sp_delete_job @job_name=N'SampleJob - 2', @delete_unused_schedule=1 GO
Example 6: Schedules Used by Multiple Jobs
Here is a query to identify the shared schedules. A periodic review may be needed to validate if this is intended.
;WITH ns AS
(SELECT sh.schedule_id, COUNT(sh.job_id) AS count_jobs
FROM msdb.dbo.sysjobschedules sh
GROUP BY sh.schedule_id
HAVING COUNT(sh.job_id) > 1
)
SELECT sh.schedule_id, sh.[name] AS schedule_name, j.[name] AS job_name, sh.[enabled]
FROM ns
JOIN msdb.dbo.sysjobschedules s
ON ns.schedule_id = s.schedule_id
JOIN msdb.dbo.sysjobs j
ON s.job_id = j.job_id
JOIN msdb.dbo.sysschedules sh
ON s.schedule_id=sh.schedule_id
ORDER BY sh.[name]
Example 7: Schedules Attached to Disabled Jobs
SELECT j.[name] AS job_name, j.[enabled] job_enabled, s.schedule_id, sh.[name] AS schedule_name, sh.[enabled] schedule_enabled, MAX(h.run_date) AS last_ran_hist, MAX(v.last_run_date) AS last_ran
FROM msdb.dbo.sysjobs j
JOIN msdb.dbo.sysjobschedules s
ON j.job_id = s.job_id
JOIN msdb.dbo.sysschedules sh
ON s.schedule_id=sh.schedule_id
LEFT JOIN msdb.dbo.sysjobhistory h
ON j.job_id= h.job_id
LEFT JOIN msdb.dbo.sysjobservers v
ON j.job_id= v.job_id
WHERE j.[enabled] = 0
GROUP BY j.[name], j.[enabled], s.schedule_id, sh.[name], sh.[enabled]
Some of these schedules may be shared, so a check is required.
Example 8: Misleading Schedules Names
It is difficult to write a query that will identify misleading schedule names. A one-time full review is most likely required to ensure there are no such schedules. After this review, update the schedule name if changes were made to the schedule's time, frequency, etc., if it is part of the name, and document the changes.
Putting It All Together
To view the full report with all details and notes about potential issues, you can run this query:
;WITH
multi_sch AS
(SELECT j.job_id, COUNT(sh.schedule_id) AS count_schedules
FROM msdb.dbo.sysjobs j LEFT JOIN msdb.dbo.sysjobschedules sh
ON j.job_id = sh.job_id
GROUP BY j.job_id
HAVING COUNT(sh.schedule_id) > 1),
multi_job AS
(SELECT sh.schedule_id, COUNT(sh.job_id) AS count_jobs
FROM msdb.dbo.sysjobschedules sh
GROUP BY sh.schedule_id
HAVING COUNT(sh.job_id) > 1),
job_hist AS
(SELECT j.job_id, MAX(ISNULL(h.run_date, 0)) AS last_ran_hist, MAX(ISNULL(v.last_run_date, 0)) AS last_ran
FROM msdb.dbo.sysjobs j
LEFT JOIN msdb.dbo.sysjobhistory h
ON j.job_id= h.job_id
LEFT JOIN msdb.dbo.sysjobservers v
ON j.job_id= v.job_id
GROUP BY j.job_id)
SELECT j.[name] AS job_name,
j.originating_server_id,
j.[enabled] AS is_job_enabled,
j.date_modified,
job_hist.last_ran,
count_schedules multiple_schedules_per_job,
sh.[name] AS sch_name,
sh.schedule_id,
sh.originating_server_id,
sh.[enabled] AS is_sch_enabled,
sh.active_start_date,
sh.version_number,
count_jobs multiple_jobs_per_schedule,
a.[name] AS alert_name,
a.last_occurrence_date,
CASE WHEN job_hist.last_ran = 0 THEN 'Job Never Ran; ' ELSE '' END +
CASE WHEN sh.[name] IS NULL THEN 'Job without schedule; ' ELSE '' END +
CASE WHEN job_hist.last_ran < CAST(REPLACE(CAST(CAST(DATEADD(DAY, -60, GETDATE()) AS DATE) AS CHAR(10)),'-','') AS INT)
AND j.[enabled] = 1 AND job_hist.last_ran > 0 AND sh.[name] IS NOT NULL THEN 'Old job (didn''t run at least 60 days); ' ELSE '' END +
CASE WHEN j.originating_server_id <> 0 THEN 'Job From Remote Server, can''t update locally; ' ELSE '' END +
CASE WHEN sh.[name] IS NULL AND job_hist.last_ran >= CAST(REPLACE(CAST(CAST(DATEADD(MONTH, -1, GETDATE()) AS DATE) AS CHAR(10)),'-','') AS INT)
THEN 'Potentially started by other job(s) or executed manually... (checking here 1 month of history only); ' -- the job also might be disabled recently or started by an alert
ELSE '' END +
CASE WHEN sh.[name] IS NULL AND job_hist.last_ran <> 0 AND a.[name] IS NOT NULL THEN 'Potentially started by (an) Alert(s); '
ELSE '' END +
CASE WHEN count_schedules >=2 THEN 'Multiple Schedules per job; ' ELSE '' END +
CASE WHEN count_jobs >=2 THEN 'Multiple Jobs share (a) Schedule(s); ' ELSE '' END +
CASE WHEN j.[enabled] = 0 THEN 'Disabled Job; ' ELSE '' END +
CASE WHEN sh.[enabled] = 0 THEN 'Disabled Schedule; ' ELSE '' END +
CASE WHEN sh.[enabled] = 0 AND sh.active_start_date > CAST(REPLACE(CAST(CAST(GETDATE() AS DATE) AS CHAR(10)),'-','') AS INT)
THEN 'Disabled Schedule with Active Start Date in the Future!!!; ' ELSE '' END +
CASE WHEN sh.version_number > 1 THEN 'Schedule Modified after creation, has versions; ' ELSE '' END AS [notes]
FROM msdb.dbo.sysjobs j
JOIN job_hist
ON j.job_id= job_hist.job_id
LEFT JOIN msdb.dbo.sysjobschedules s
ON j.job_id = s.job_id
LEFT JOIN msdb.dbo.sysschedules sh
ON s.schedule_id=sh.schedule_id
LEFT JOIN multi_sch ms
ON j.job_id = ms.job_id
LEFT JOIN multi_job mj
ON s.schedule_id = mj.schedule_id
LEFT JOIN msdb.dbo.sysalerts a
ON j.job_id = a.job_id
ORDER BY j.originating_server_id, j.[enabled], sh.[enabled], j.[name], sh.[name]
Here is a shorter version ("Schedules Summary report" of the jobs'/schedules' potential issues). It will only show job name, schedule name, and notes (empty notes mean – "no issues"):
;WITH
multi_sch AS
(SELECT j.job_id, COUNT(sh.schedule_id) AS count_schedules
FROM msdb.dbo.sysjobs j LEFT JOIN msdb.dbo.sysjobschedules sh
ON j.job_id = sh.job_id
GROUP BY j.job_id
HAVING COUNT(sh.schedule_id) > 1),
multi_job AS
(SELECT sh.schedule_id, COUNT(sh.job_id) AS count_jobs
FROM msdb.dbo.sysjobschedules sh
GROUP BY sh.schedule_id
HAVING COUNT(sh.job_id) > 1),
job_hist AS
(SELECT j.job_id, MAX(ISNULL(h.run_date, 0)) AS last_ran_hist, MAX(ISNULL(v.last_run_date, 0)) AS last_ran
FROM msdb.dbo.sysjobs j
LEFT JOIN msdb.dbo.sysjobhistory h
ON j.job_id= h.job_id
LEFT JOIN msdb.dbo.sysjobservers v
ON j.job_id= v.job_id
GROUP BY j.job_id)
SELECT j.[name] AS job_name,
sh.[name] AS sch_name,
CASE WHEN job_hist.last_ran = 0 THEN 'Job Never Ran; ' ELSE '' END +
CASE WHEN sh.[name] IS NULL THEN 'Job without schedule; ' ELSE '' END +
CASE WHEN job_hist.last_ran < CAST(REPLACE(CAST(CAST(DATEADD(DAY, -60, GETDATE()) AS DATE) AS CHAR(10)),'-','') AS INT)
AND j.[enabled] = 1 AND job_hist.last_ran > 0 AND sh.[name] IS NOT NULL THEN 'Old job (didn''t run at least 60 days); ' ELSE '' END +
CASE WHEN j.originating_server_id <> 0 THEN 'Job From Remote Server, update locally; ' ELSE '' END +
CASE WHEN sh.[name] IS NULL AND job_hist.last_ran >= CAST(REPLACE(CAST(CAST(DATEADD(MONTH, -1, GETDATE()) AS DATE) AS CHAR(10)),'-','') AS INT)
THEN 'Potentially started by other job(s) or executed manually... (checking here 1 month of history only); ' -- the job also might be disabled recently or started by an alert
ELSE '' END +
CASE WHEN sh.[name] IS NULL AND job_hist.last_ran <> 0 AND a.[name] IS NOT NULL THEN 'Potentially started by (an) Alert(s); '
ELSE '' END +
CASE WHEN count_schedules >=2 THEN 'Multiple Schedules per job; ' ELSE '' END +
CASE WHEN count_jobs >=2 THEN 'Multiple Jobs share (a) Schedule(s); ' ELSE '' END +
CASE WHEN j.[enabled] = 0 THEN 'Disabled Job; ' ELSE '' END +
CASE WHEN sh.[enabled] = 0 THEN 'Disabled Schedule; ' ELSE '' END +
CASE WHEN sh.[enabled] = 0 AND sh.active_start_date > CAST(REPLACE(CAST(CAST(GETDATE() AS DATE) AS CHAR(10)),'-','') AS INT)
THEN 'Disabled Schedule with Active Start Date in Future!!!; ' ELSE '' END +
CASE WHEN sh.version_number > 1 THEN 'Schedule Modified after creation, has versions; ' ELSE '' END AS [notes]
FROM msdb.dbo.sysjobs j
JOIN job_hist
ON j.job_id= job_hist.job_id
LEFT JOIN msdb.dbo.sysjobschedules s
ON j.job_id = s.job_id
LEFT JOIN msdb.dbo.sysschedules sh
ON s.schedule_id=sh.schedule_id
LEFT JOIN multi_sch ms
ON j.job_id = ms.job_id
LEFT JOIN multi_job mj
ON s.schedule_id = mj.schedule_id
LEFT JOIN msdb.dbo.sysalerts a
ON j.job_id = a.job_id
ORDER BY j.[name], sh.[name]
Cleanup the Schedules
Here are some examples of how and when to clean up schedules.
Example 1: Delete a schedule during a job deletion (considering the schedule is not used by any other job)
EXEC msdb.dbo.sp_delete_job @job_name=N'SampleJob - 2', @delete_unused_schedule=1 GO
Note: The schedule will be deleted only if it is not used by other jobs. If it is still in use, only the job will be deleted.
Example 2: Remove/detach a schedule from a job
EXEC msdb.dbo.sp_detach_schedule @job_name='MEGEnergyDW Adhoc', @schedule_name='Daily' GO
Example 3: Run a script to delete the unused schedule
EXEC msdb.dbo.sp_delete_schedule @schedule_name='Daily' GO
Note: You cannot delete the schedule if it's still used by other jobs.
![Msg 14372, Level 16, State 1, Procedure msdb.dbo.sp_delete_schedule, Line 48 [Batch Start Line 68]
The schedule was not deleted because it is being used by one or more jobs.](/tipimages2/7393_sql-server-agent-job-schedules-review.020.png)
Considerations for Better Schedules Management
Using the scripts provided, you can run the schedules report before:
- Sharing a schedule
- Disabling a schedule
- Enabling a schedule
- Deleting a schedule
- Attaching a schedule to a job
- Detaching a schedule from a job
- Updating a schedule
Documentation. It is important to include schedules in the DBA's documentation. Make sure to add more details if there is a business reason to run a job using a specific schedule. Sometimes there are dependencies, i.e., one job must always run before the other. Make notes about these dependencies as well.
Naming. Use meaningful non-repeating schedule names to avoid confusion and unexpected missing job runs or to avoid double runs of the jobs (if a schedule was updated).
Reminders. Set a reminder to review the schedules, i.e., if a schedule was disabled temporarily or if the active start date is in the future.
Schedules seem like a basic component to manage. But sometimes we do not realize that a simple schedule change may have an unexpected impact on SQL Server Agent jobs.
Next Steps
- Read other SQL Server Agent Tips
- This tip helps to Generate SQL Agent Job Schedule Report, which is more readable than just a query to the sysschedules table.
- Read SQL Server Agent Job Management
About the author
Svetlana Golovko is a DBA with 13 years of the IT experience (including SQL Server and Oracle) with main focus on performance. This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
Article Last Updated: 2022-09-20
