T-SQL script to show all disabled SQL Server Agent schedules that have jobs

Problem: SQL Server Agent jobs on your server are enabled and have at least one schedule assigned to them, but they do not run at the scheduled date/time.

Why? There could be a number of reasons, but one that is easily overlooked is because an underlying schedule is disabled.

The following script when run on your SQL Server instance will show all schedules that are disabled but more importantly will show those schedules that have jobs associated with them. This is important because it will help identify those jobs that may not run because the schedule has been disabled. The last thing any concerned DBA will want is a job that has been scheduled but will not run because the underlying schedule is not functional.

select schedule_id, [name]
from msdb.dbo.sysschedules
where enabled = 0 and schedule_id in
(select distinct schedule_id from msdb.dbo.sysjobschedules)

Explanation: The MSDB table dbo.sysschedules contains information on each individual schedule within the agent. Additionally, the MSDB table dbo.sysjobschedules contains schedule information for jobs that are to be scheduled by the agent.
If the job_id is in dbo.sysjobschedules, but the underlying schedule is disabled, the job will not run. This could pose a potential problem area in your environment.

Did you find this useful? Let me know in the comments below. Cheers.

Note: this blog post was originally created on January 15, 2015.