Problem: SQL Server Agent jobs on your server are enabled but they do not run at the scheduled date/time.
Why? There could be a number of reasons, one of which could be because the underlying schedule is not enabled as we’ve seen in this post. However, some jobs may not run (even though they are enabled) because they have no schedule assigned to the job at all.
The following script can be run on your SQL Server instance to determine which jobs are enabled but do not have a schedule assigned to them.
select * from msdb.dbo.sysjobs where enabled = 1 and job_id not in (select job_id from msdb.dbo.sysjobschedules)
Explanation: If the job_id is not in the dbo.sysjobschedules table but the underlying job is enabled within dbo.sysjobs, the job will still not run automatically, this is by design. One can however manually run the job if desired via the system stored procedure in the MSDB database, dbo.sp_start_job.
External Links: The MSDB table dbo.sysjobs stores the information for each scheduled jobs to be executed by the SQL Server Agent. Additionally, the MSDB table dbo.sysjobschedules contains schedule information for jobs that are to be scheduled by the agent. The MSDB system stored procedure dbo.sp_start_job, instructs SQL Server Agent to start a job immediately.
Did you find this useful? Let me know in the comments below. Cheers.
Note: this blog post was originally created on August 20, 2014.