SSRS Schedules

Overview

SSRS Schedules shows every clock on a report server and what hangs off each one.

A schedule is what makes a report server do work on its own. Subscriptions, cache refreshes and snapshot updates are all rows in the catalog’s Schedule table, each with a SQL Server Agent job named after it, and the report server offers no single place to see them all.

What this page is for:

  • Shared against report specific. A shared schedule carries any number of subscriptions on one Agent job, so a change to it reaches all of them. The count of what rides on each schedule is the first column worth reading.
  • Orphans. A schedule with nothing riding on it belongs to nothing, and its Agent job still wakes SQL Server up on its cadence. The usual one is a shared schedule nobody uses any more: the catalog removes a report specific schedule by itself when its last use goes, but never a shared one.
  • Schedules that should have fired and did not. A next run time more than an hour in the past means the report server service has not moved it forward, which points at the service rather than at a delivery destination.
  • Schedules that have expired. A schedule past its end date will never run again and looks normal in the portal.

Where to find it

Only shown for a database holding a Reporting Services catalog.

  • Tree: expand the report server database, then Real Time > SSRS > Schedules.
  • Subscriptions: press Schedules on the toolbar of SSRS Subscriptions, or choose Open Schedules from its right click menu.
  • Delivery Queue: press Schedules on the toolbar of SSRS Delivery Queue.
  • Caching and Execution: press Schedules on the toolbar of SSRS Caching and Execution, or choose The schedules behind these from its right click menu.

Requirements

  • A Reporting Services catalog database. The SSRS pages are offered when dbo.ExecutionLog and dbo.Catalog both exist.
  • SELECT on dbo.Schedule, dbo.ReportSchedule, dbo.Catalog and dbo.Users.
  • SELECT on msdb.dbo.sysjobs_view and msdb.dbo.syscategories for the State column. On a default msdb both are granted to SQLAgentUserRole, but sysjobs_view shows a login only the jobs it owns unless it is sysadmin or a member of SQLAgentReaderRole (which SQLAgentOperatorRole and RSExecRole include). The page checks the permission before reading, so a login without it still gets the page, with State reading Unknown.

The two views

The toolbar switches between By what rides and By next run.

By what rides

Schedules with the most riding on them first: subscriptions plus report level uses. The value on the right reads 12 riders, or nothing for a schedule no subscription or item uses.

By next run

Schedules in the order they next fire, soonest first. The value on the right reads in 20 minutes, in 5 hours, due now, overdue since 3 hours ago, disabled, expired or not set. A schedule with no next run time sorts to the far end and draws no bar, so the bars that are drawn keep a readable scale.

Reading the bars

Each bar is labeled with the schedule’s name. A schedule with no name is labeled with the item it drives, (orphaned schedule) when it drives nothing, or (unnamed schedule).

Mark Meaning
Green bar Nothing found wrong
Amber bar Expired, or its Agent job is disabled
Red bar Orphaned, overdue, or its last run status reads as a failure
Amber outline Orphaned, overdue or expired

Hover over a bar for the kind, the recurrence and window, an example of what rides on it, when it last ran, its state and the notes.

Reading the grid

Column What it holds
Schedule The schedule name, or the label used on the chart
State Enabled or Disabled from the Agent job, or Unknown when no job was visible
Notes What the page derived about the row
Last status The report server’s own last run status
Kind Shared or Report specific, followed by its purpose where the schedule’s event type says one, such as scheduled delivery, cache expiry, snapshot refresh or report history snapshot
Rides on it The subscriptions plus the report level uses on this schedule
Recurrence The recurrence in words, such as Every 15 minutes or Monthly, first Mon
Next run How soon it next fires, as on the chart
Last run When it last ran, or never
Window The start and end dates, such as from 1 Mar 2025 until 31 Dec 2026
Created by The account that created the schedule

Layout. The columns that say whether a schedule is healthy (State, Notes and Last status) come straight after Schedule, so they are on screen at 1280 x 900 without scrolling sideways. Every text column is capped, Created by takes all the width that is left (never less than 120 px), and a value that is still cut off shows in full in the tooltip when the mouse rests on its row.

State and Notes are colored when the schedule is not in a healthy state. The Notes column says, where it applies:

  • that nothing rides on the schedule and its Agent job is still waking SQL Server up, and for a report specific schedule, that the catalog normally removes those by itself
  • that it is past its end date and will never run again
  • when its next run was, and that the time has not moved
  • that one Agent job carries all of the riders on a shared schedule, so switching it reaches every one
  • that no Agent job of this name was visible to this login
  • the last run status, when it reads as a failure

Findings

The headline counts the schedules, how many are shared, and how many drive nothing, or says every one of them drives something. The line under it adds, where they apply:

  • That an orphaned schedule still has a live Agent job, and that deleting its Schedule row removes that job too, through the catalog’s own Schedule_DeleteAgentJob trigger.
  • How many schedules are more than an hour past their next run time.
  • How many schedules are past their end date.
  • How many Agent jobs are disabled.
  • The busiest schedule and how many riders it carries on one Agent job.
  • That this login has no SELECT on msdb.dbo.sysjobs_view, so State reads Unknown throughout. Otherwise, when the login could read Agent’s jobs and found none in the Report Server category, that the state column cannot say whether these fire.

Actions

The grid allows more than one row to be selected.

  • Click a bar to select that schedule’s row in the grid.
  • Double click a bar to select its row and move the focus to the grid.
  • Right click a bar for the same menu as right clicking its row in the grid.
  • Right click one or more rows for:
    • Copy the Agent job name, for the first row selected.
    • Copy the script that removes this orphaned schedule (or these orphaned schedules), shown only when the selection includes an orphaned schedule, followed by Open the cleanup script in SSMS when SSMS is installed.
    • Open Subscriptions (opens SSRS Subscriptions) and Open Caching and Execution (opens SSRS Caching and Execution). Both open for the whole server; neither filters to the schedules selected.
  • Right click an empty part of the chart to copy the chart to the clipboard.
  • The toolbar buttons Subscriptions and Caching open those pages.

The orphan cleanup script

Nothing is removed from this page. The script covers only the orphaned schedules in the selection, under a comment explaining what it does. For each one it writes:

  • a DELETE from the catalog’s Schedule table that runs only if no ReportSchedule row points at the schedule. The page read the catalog once when it loaded, so this checks again, and does nothing for a schedule something has started using since.
  • nothing separate for the Agent job in the normal case. The catalog’s Schedule_DeleteAgentJob trigger runs msdb.dbo.sp_delete_job for the job named after the schedule in the same statement, which is also what the portal does when a shared schedule is deleted there.
  • a guarded sp_delete_job that runs only if a job of that name is still there after its Schedule row has gone, so on a normal run it does nothing.

The login running the script needs rights to delete those Agent jobs. Take a backup of the catalog database first.

Where the data comes from

dbo.Schedule joined to dbo.Users for the creator. For each schedule, dbo.ReportSchedule is counted twice: rows with a subscription id are subscriptions (a cache refresh plan is one of these), and rows without one are items, such as a cache expiry or snapshot schedule attached straight to a report. The first report path in alphabetical order across the schedule’s ReportSchedule rows is read as the example shown in the tooltip, and its last part is the label for an unnamed schedule.

A second read lists the jobs in the Report Server category from msdb.dbo.sysjobs_view, matched on this side by job name, which is the schedule id. That read runs only when HAS_PERMS_BY_NAME says the login may, so a refusal leaves State at Unknown rather than failing the page.

Kind reads the catalog’s Schedule.Type, where 0 is a shared schedule and 1 is a schedule created for one subscription or report. The purpose after it comes from Schedule.EventType; an event type the page does not recognize is shown split into words rather than as one run together name.

Messages you may see

Nothing on this report server runs on a clock. There are no schedules in the catalog, so no subscription fires on its own, no cache is refreshed on a cycle and no snapshot is taken unattended. That is normal for a report server people only visit.

The report server query did not finish in time. Press F5 to try again.

Frequently asked questions

What is a rider? Anything that runs on the schedule: a subscription, or a report item such as a cache expiry or snapshot schedule. Rides on it adds the two counts together; no ReportSchedule row is counted in both.

Why does a schedule show as overdue? Its next run time is more than an hour in the past and it is neither expired nor disabled. The report server service moves that time forward as it runs the schedule, so a time that has stopped moving points at the service.

Is an orphaned schedule harmful? It does no report work, but its Agent job still starts on the schedule’s cadence. The page offers a cleanup script rather than removing anything, so you can confirm nothing uses it first.