Job Schedules

Overview

The schedule of a SQL Server Agent job is stored as five integers with bit fields in them. Reading it from msdb directly means decoding freq_type, freq_interval, freq_relative_interval, freq_recurrence_factor and freq_subday_type in your head, and getting one of them wrong is easy.

The Job Schedules report decodes all of it and writes each schedule out in English: Weekly, Every 1 weeks(s) on Saturday, Occurs once at 02:00:00.

Then it adds the three things you actually wanted next.

  • Is it on? The job and the schedule are enabled separately, and both are shown.
  • How long does it take? The average run time across the retained history.
  • When does it run next? Straight from the Agent’s own answer.

This is the planning page. Job History is what happened and Failed Jobs is what went wrong; this one is what is going to happen and how long it will take.

The Job Schedules report
Every job on the instance with its schedule written out in words.

Where to find it

An instance level report. Right-click the server → Instance Level Reports → Job Schedules.

The page title reads Job Schedules for <server name>.

Requirements

  • SQL Server Agent. No Agent means no msdb job tables and an empty page. That is the expected result on SQL Server Express, which has no Agent at all.
  • Access to msdb.dbo.sysschedules. The query checks first and returns nothing rather than an error when the permission is not there. SQLAgentReaderRole in msdb is enough; so is sysadmin.

Nothing on this page changes anything. There is no enable, disable or run action here.

Reading the grid

Column What it is
Instance @@SERVERNAME. Present so the page still reads correctly when several instances are appended into one view.
Job Name The Agent job.
Owner The login that owns the job, resolved from the owner SID.
Enabled Yes when the job itself is enabled.
Scheduled Yes when the schedule attached to it is enabled.
Description The job’s description, with line breaks flattened so it stays on one row.
Occurs The broad recurrence: Once, Daily, Weekly, Monthly, Monthly relative, When SQL Server Agent starts, or Start whenever the CPU(s) become idle.
Schedule The recurrence spelled out, for example Every 2 weeks(s) on Monday, Wednesday or The Third Friday of every 1 month(s).
Frequency What happens within a day, for example Occurs once at 02:00:00 or Occurs every 15 Minute(s) between 06:00:00 and 22:00:00.
Avg Run (sec) Average duration in seconds across every job outcome in the retained history.
Next Run When the Agent expects to run it next.

Enabled and Scheduled are two different switches

A job can be enabled with a disabled schedule, or disabled with a live schedule. Both read as half on, and both are common ways for a job to quietly stop running without anybody noticing.

  • Enabled = Yes, Scheduled = No. The job works if you start it by hand, and it will never start itself.
  • Enabled = No, Scheduled = Yes. The schedule is intact and waiting. Re-enabling the job puts it straight back into service, which is exactly what you want after maintenance and exactly what you do not want if it was disabled for a reason.

One row per schedule, not per job

A job with three schedules gets three rows, one per schedule, because the schedule is what the row is about. A job with no schedule still gets a row, with the schedule columns blank, because a job nobody has scheduled is one of the things worth finding here.

The job schedules grid
Enabled and Scheduled are separate columns because they are separate switches.

Reading Next Run

Next Run comes from the Agent’s own next_run_date, so it is the Agent’s answer rather than a calculation this report makes.

A Next Run of 1900-01-01 means there is no next run. The Agent stores zero when a schedule will not fire again: the job is disabled, the schedule is disabled, it was a one-time schedule that has already run, or it only runs on Agent startup or CPU idle. The date is what zero converts to, and it is a placeholder rather than a real answer.

Next Run goes stale when the Agent is stopped. The Agent maintains that column; nothing else does. A stopped Agent leaves whatever was last written there, which will be in the past.

Reading Avg Run (sec)

The average is taken across every job outcome row in sysjobhistory, which is the row the Agent writes when the whole job finishes rather than one per step. Two things follow from that.

  • It includes failures. A job that fails fast pulls its own average down, which can make a problem job look quick.
  • It only covers the history that is still there. msdb history is purged, usually by a maintenance job, so on an instance with aggressive purging the average is over the last few days rather than the last year.

A blank means no completed run is left in the history at all.

How to read the report

  1. Sort on Next Run and read the night. Everything that runs between midnight and 06:00 in one block, in order, is the maintenance window as it actually is rather than as it was designed.
  2. Add Avg Run to the start times. Two jobs that start twenty minutes apart, where the first averages forty minutes, are overlapping every night.
  3. Look for Enabled = No. A disabled job is either deliberate or a forgotten workaround. There is no third case.
  4. Look for Scheduled = No on an enabled job. Somebody turned the schedule off instead of the job, which is the quieter way to stop a backup from running.
  5. Look for a blank Owner. The owning login no longer exists, and the job may not be running at all.
  6. Check Frequency on anything that runs sub-daily. Occurs every 10 Seconds(s) is usually a copy of a job somebody meant to run every ten minutes.

Common patterns

A backup job with Scheduled = No. The single most valuable thing this page finds. Backup Status will show the gap as an exposure; this page shows the reason.

Two heavy jobs overlapping. Index maintenance and a full backup starting at the same time on the same volume. I/O by Drive and Disk Latency by Hour by Day show the cost.

A blank Owner. SUSER_SNAME could not resolve the owner SID, meaning the login was dropped or belonged to a domain account that no longer exists. Jobs owned by a missing principal are a common cause of failures that look like nothing.

Avg Run climbing on a job you have looked at before. A maintenance job growing with the data. Job History shows the trend rather than the average.

Occurs = When SQL Server Agent starts. Fine and deliberate for a warm-up or a cleanup job, and worth confirming it is meant to be that rather than a schedule somebody never finished setting up.

Next Run in the past on every row. The Agent is stopped. Nothing on this instance is running on a schedule at all.

Where the data comes from

  • msdb.dbo.sysjobs for the job, its owner and whether it is enabled.
  • msdb.dbo.sysjobschedules and msdb.dbo.sysschedules for the schedule and the next run, joined outward so an unscheduled job still appears.
  • msdb.dbo.syscategories for the job category.
  • msdb.dbo.sysjobhistory, restricted to step_id = 0, for the average duration.

run_duration is stored as an HHMMSS integer rather than a count of seconds, so it is decomposed into hours, minutes and seconds and reassembled before being averaged. Treating it as a number directly is the usual way this figure comes out wrong.

Nothing is stored by this page.

Report Why you would go there
Job History What actually ran, when, and how long each run really took.
Failed Jobs Which jobs are failing, how often, and when they last succeeded.
Backup Status Whether the backup jobs on this page are producing backups.
Long Running by Hour Whether the schedule collides with the application’s busy hours.
Disk Latency by Hour by Day What the maintenance window costs the storage.
Error Log The Agent log, for jobs that fail before they get as far as writing history.

Frequently asked questions

Why does one job appear several times? Because it has more than one schedule, and the row is about the schedule. Three schedules, three rows.

Why is the Next Run 1900-01-01? Because the Agent stored zero for it, which means there is no next run: disabled, already run, or a schedule type that has no predictable next time.

Why are the schedule columns blank on some rows? Because that job has no schedule attached. The row is there so you can see that.

Why is the Owner blank? Because the owning SID no longer resolves to a login. That is worth fixing, and it is a common cause of jobs failing to start.

The report is empty. Either SQL Server Agent is not installed, which is the normal case on Express, or the connection has no access to msdb.dbo.sysschedules. The query checks the permission and returns nothing rather than raising an error.

Why does Avg Run not match what I see in Job Activity Monitor? Because this is an average across the retained history including failed runs, not the duration of the last run. Job History has the run by run figures.

Can I enable, disable or start a job from here? No. This page reads.