SQL Server Agent Jobs Schedules - Review, Cleanup, and Special Cases

By:   |   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.

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









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.

View all my tips


Article Last Updated: 2022-09-20

Comments For This Article