Tip: Jobs without a scheduled run

It’s awfully comforting to see all those backup and maintenance jobs in your SQL Agent list, isn’t it? It’d be a shame if, you know, they never ran.

Where do we keep our job information? In MSDB tables:

• Your jobs are listed in dbo.sysjobs.
• Schedules are in dbo.sysschedules.
• The table dbo.sysjobschedules joins them together. (Remember, a job can have more than one schedule, just as a schedule can be assigned to more than one job.)

Jobs without schedules

Let’s see what jobs have no schedules. To do that, we really just need to check jobs in sysjobs against the sysjobschedules table:

SELECT j.name, s.schedule_id, s.next_run_date, s.next_run_time
FROM dbo.sysjobs j
LEFT OUTER JOIN dbo.sysjobschedules s ON j.job_id = s.job_id
WHERE s.job_id IS NULL;


That query will show us which jobs simply have no associated schedules. Now, what if a job has a schedule, but the schedule itself is not enabled?

Jobs with disabled schedules

Well, now we have to actually check the sysschedules table for the enabled flag:

USE msdb;
GO
SELECT j.name, s.schedule_id, s.next_run_date, s.next_run_time, ss.enabled
FROM dbo.sysjobs j
LEFT OUTER JOIN dbo.sysjobschedules s ON j.job_id = s.job_id
INNER JOIN dbo.sysschedules ss ON ss.schedule_id = s.schedule_id
WHERE s.job_id IS NULL
OR ss.enabled=0;


I built on the original query: this gets us any cases where a job has no entry in sysjobschedules, OR the schedule it’s associated with is not enabled.

Of course, not all jobs should have schedules. But if you happen to see “DBA – Nightly Backup of Important Production Databases OMG I Hope This Runs” job, it’s a good bet that it should be running on a scheduled basis.

Happy days,
Jen McCown
MidnightDBA.com/Jen

P.S. Bonus points: Check for jobs that don’t alert on failure, jobs that have failed recently, jobs owned by user accounts. What else?