List SQL Server Agent jobs and their schedules
List every SQL Server Agent job with its schedule in words, next run and last outcome, on all your servers, in SQLTreeo Job Timeline. Or with a T-SQL query.
You can list every SQL Server Agent job with its schedule in plain words, its next run and its last outcome in the Jobs view of SQLTreeo Job Timeline, for all your servers together.
That is the list you want before you move a server, tidy up its jobs or plan a maintenance window: which jobs exist, when they run and whether they still do. This article gives the views, and further down a T-SQL query that puts the same information in one result set for one server, what its codes mean, and a second query for jobs that never start on their own.
All servers, and how the schedules fit together
SQLTreeo Job Timeline reads the jobs and schedules of all the servers you add, with only SELECT statements and nothing installed on them:
- The Jobs view lists every job with its schedule in plain words, the next run, the last run with its duration and outcome, the typical duration, the number of steps and the availability group it works for. The switches Only enabled, Only scheduled and Last run failed narrow the list.
- The Rhythm view folds the days onto one 24-hour axis and shows where the jobs start, how many run at once, and which jobs overlap. See Find overlapping SQL Server Agent jobs.
- The Servers view has one row per server, so you can see which servers have failed runs, or jobs that run too long.
With T-SQL: the tables
SSMS shows a job’s schedule in a dialog, one job at a time. The same information is in msdb, spread over four tables and stored as numbers.
| Table | What it holds |
|---|---|
msdb.dbo.sysjobs |
One row per job: name, enabled or not |
msdb.dbo.sysjobschedules |
Which schedule belongs to which job, and the next run |
msdb.dbo.sysschedules |
The schedules themselves: frequency, interval, start time |
msdb.dbo.sysjobservers |
The last outcome of the job (server_id = 0 is this server) |
A job can have several schedules, and a schedule can be shared by several jobs, so a job with two schedules appears twice in the result.
Jobs with their schedules, next run and last outcome
SELECT j.name AS job_name,
j.enabled AS job_enabled,
sc.name AS schedule_name,
sc.enabled AS schedule_enabled,
CASE sc.freq_type WHEN 1 THEN 'once' WHEN 4 THEN 'daily' WHEN 8 THEN 'weekly' WHEN 16 THEN 'monthly'
WHEN 32 THEN 'monthly, relative' WHEN 64 THEN 'when Agent starts' WHEN 128 THEN 'when idle' END AS frequency,
sc.freq_interval,
CASE sc.freq_subday_type WHEN 1 THEN 'once a day'
WHEN 2 THEN CONCAT('every ', sc.freq_subday_interval, ' seconds')
WHEN 4 THEN CONCAT('every ', sc.freq_subday_interval, ' minutes')
WHEN 8 THEN CONCAT('every ', sc.freq_subday_interval, ' hours') END AS within_the_day,
STUFF(STUFF(RIGHT('000000' + CAST(sc.active_start_time AS varchar(6)), 6), 5, 0, ':'), 3, 0, ':') AS from_time,
CASE WHEN js.next_run_date > 0 THEN msdb.dbo.agent_datetime(js.next_run_date, js.next_run_time) END AS next_run,
CASE srv.last_run_outcome WHEN 0 THEN 'failed' WHEN 1 THEN 'succeeded' WHEN 3 THEN 'canceled' WHEN 5 THEN 'unknown' END AS last_outcome
FROM msdb.dbo.sysjobs AS j
LEFT JOIN msdb.dbo.sysjobschedules AS js ON js.job_id = j.job_id
LEFT JOIN msdb.dbo.sysschedules AS sc ON sc.schedule_id = js.schedule_id
LEFT JOIN msdb.dbo.sysjobservers AS srv ON srv.job_id = j.job_id AND srv.server_id = 0
ORDER BY j.name, sc.name;
The joins are LEFT joins, so a job without a schedule is in the list too, with empty schedule columns. next_run is empty for a job that is disabled or has no upcoming run.
What freq_interval means
The meaning of freq_interval depends on freq_type:
| freq_type | freq_interval |
|---|---|
| 4 (daily) | Every N days |
| 8 (weekly) | A sum of the days: 1 Sunday, 2 Monday, 4 Tuesday, 8 Wednesday, 16 Thursday, 32 Friday, 64 Saturday. Monday to Friday is 2 + 4 + 8 + 16 + 32 = 62. |
| 16 (monthly) | The day of the month |
| 32 (monthly, relative) | The day: 1 Sunday to 7 Saturday, 8 day, 9 weekday, 10 weekend day. freq_relative_interval says which one of the month: first, second, third, fourth or last. |
For a weekly schedule, freq_recurrence_factor is the number of weeks between runs. The query leaves freq_relative_interval and freq_recurrence_factor out to stay readable, and adds the plain description in within_the_day for schedules that repeat during the day.
Enabled jobs with no enabled schedule
A job that is enabled but has no enabled schedule does not start by itself. That is sometimes on purpose, and sometimes the result of a schedule that somebody disabled and forgot.
SELECT j.name AS job_name
FROM msdb.dbo.sysjobs AS j
WHERE j.enabled = 1
AND NOT EXISTS (SELECT 1
FROM msdb.dbo.sysjobschedules AS js
JOIN msdb.dbo.sysschedules AS sc ON sc.schedule_id = js.schedule_id
WHERE js.job_id = j.job_id AND sc.enabled = 1)
ORDER BY j.name;
Read the list with care. A job can be started by another job (sp_start_job in a step), by an alert, or by an application, and does not need a schedule. Those jobs are in the list although nothing is wrong.
Run the queries on many servers
These queries run on one server. With a Central Management Server you can run them on a group of servers, as described in Find failed SQL Server Agent jobs on all your servers. A list does not show how the schedules fit together: two jobs at 02:00 look innocent in a table, and that is what the Rhythm view is for.
Next steps
- Download SQLTreeo Job Timeline. The trial covers every feature and any number of servers, and the tool stays free for up to three servers afterwards.
- Getting started adds the first server and shows the views.
- Add many servers at once from a list, a Central Management Server, SSMS or the network.