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:

SQL Agent job with multiple schedules

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
Initial Schedules (Microsoft)

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'
msdb Database creation date

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:

Manage schedules in 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":

Schedule accessed from the job properties

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.

Misleading or duplicate schedules names

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.

Missing last run date from job history table

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.

SQL Agent Properties - job history retention

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
Jobs with multiple schedules

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
Multiple schedules with the same name

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.

Jobs with Disabled 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'
Validated - Enabled schedule

Step 3: Attach the schedule to a job with SSMS or T-SQL.

Attach schedule to the job with SSMS
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'
Validate - schedule disabled

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
Unused schedules

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.

delete the schedule from the job using SSMS

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]
Schedules used by multiple jobs

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]
Schedules attached to disabled jobs

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]
Jobs schedules summary report

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]&#xA;The schedule was not deleted because it is being used by one or more 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





get scripts

next tip button



About the author
MSSQLTips author Svetlana Golovko 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

















get free sql tips
agree to terms