SSRS Delivery Queue

Overview

SSRS Delivery Queue is the page to open while the phone call is in progress and the question is “why has the six o’clock report not arrived”. Every other page in the SSRS list reads history. This one reads the present.

It reads four catalog tables. All of them should normally be nearly empty, and each means something different when it is not:

Kind Table What a row is
Delivery Notifications A report waiting to be sent. Attempts count retries, and a heartbeat is stamped while something is working on it
Event Event General work for the service, such as a subscription that has fired and not yet been turned into deliveries
Running RunningJobs A render in progress, with who asked for it and the timeout it was given
Stranded SubscriptionsBeingDeleted A subscription deletion. The catalog clears these after ten minutes

Events piling up means the report server service is not draining its queue at all, which is a different problem from deliveries that fail.

An empty page is the healthy answer, and the page says so in those words.

Where to find it

Only shown for a database holding a Reporting Services catalog.

  • Tree: expand the report server database, then Real Time > SSRS > Delivery Queue.
  • Subscriptions: press Queue on the toolbar of SSRS Subscriptions, or choose Open Delivery Queue from its right click menu.
  • Delivery History: press Queue on the toolbar of SSRS Delivery History, or choose Open Delivery Queue from its right click menu.
  • What Fills This Database: press Queue on the toolbar of SSRS What Fills This Database, or right click its Event or Notifications row and choose What is stuck in the queue.

Requirements

  • A Reporting Services catalog database. The SSRS pages are offered when dbo.ExecutionLog and dbo.Catalog both exist.
  • SELECT on dbo.Notifications, dbo.Event, dbo.RunningJobs, dbo.SubscriptionsBeingDeleted, dbo.Subscriptions, dbo.Catalog and dbo.Users.

Reading the chart

There is one view: one bar per item across all four queues, longest waiting first. Each bar is labeled with the last part of what it is waiting on, usually the report name, with its kind and detail underneath, such as Delivery, by Email, data driven.

The value on the right is how long it has waited: just now, then minutes, hours past ninety minutes, and days past two days.

Mark Meaning
Green bar Waiting, and not yet long enough to worry about
Amber bar A delivery on a retry rather than its first attempt
Red bar Stuck
Amber outline Stuck

Stuck means:

  • more than 15 minutes in the queue for a delivery or an event
  • past its own timeout for a render in progress that has one, and more than 15 minutes for one that does not
  • more than 10 minutes for a subscription deletion, which is past the window the catalog clears these in on its own

Hover over a bar for the full path, when it entered the queue, who it is for and the notes.

Reading the grid

Column What it holds
Kind Delivery, Event, Running or Stranded
What The report path for a delivery, the event type for an event, the request path or name for a render, the report path or subscription id for a deletion
Waiting How long it has been in the queue, colored when it is retrying or stuck
Since When it entered the queue, in the database server’s local time
Attempt The delivery attempt number
Next try When the service will next pick up a delivery, in the database server’s local time
Last touched The last heartbeat stamped on it, in the database server’s local time
For The subscription owner for a delivery, or the user for a render
Notes What the page derived about the row

The Notes column says, where it applies:

  • for a deletion, whether it is still in progress or did not finish
  • for a render, its timeout, or that no timeout is set so nothing will stop it
  • for a stuck event, that the report server service is not draining its queue
  • how many times a delivery has been retried
  • that nothing has touched it for more than 15 minutes, which is what a service stopped part way through leaves behind
  • the delivery method, whether it is data driven and the subscription’s description for a delivery, the id it refers to for an event, and the computer for a render

Findings

The headline counts deliveries waiting, unprocessed events, reports rendering and deletions in flight, and how many of those are stuck. The line under it adds, where they apply:

  • What stuck means, when anything is.
  • How many deliveries are on a retry, which usually means the destination refused them.
  • That events waiting with no deliveries behind them point at the report server service rather than at a delivery destination.
  • That a deletion older than 10 minutes is past the window the catalog clears these in.
  • That this is a snapshot taken when the page loaded, and F5 takes a fresh one.
  • That times are the database server’s local time and waiting times are measured on the server.

Actions

  • Click a bar to select that item’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 a row for Copy what it is waiting on, Open Subscriptions (opens SSRS Subscriptions) and Open Delivery History (opens SSRS Delivery History). Both open for the whole server; neither filters to the row selected.
  • Right click an empty part of the chart to copy the chart to the clipboard.
  • The toolbar buttons Subscriptions, Delivery history and Schedules open those pages.

Where the data comes from

Four reads in one batch, one per table, rather than a union:

  • dbo.Notifications joined to dbo.Catalog for the report, dbo.Subscriptions for the description and dbo.Users for the owner.
  • dbo.Event, with the event type and its EventData, which is the id of whatever the event is about.
  • dbo.RunningJobs joined to dbo.Users.
  • dbo.SubscriptionsBeingDeleted, joined to dbo.Subscriptions and dbo.Catalog for a report path where the subscription row still exists.

Every read is under READ UNCOMMITTED, because the report server writes to these tables constantly and a monitoring page that takes shared locks on them would block the thing it is watching.

The tables are stamped on two different clocks. The report server writes the Notifications, Event and SubscriptionsBeingDeleted times in UTC, and the RunningJobs start time in local time. So how long each item has waited, and how long since its heartbeat, is worked out on the database server against the clock that column was written with, and the times shown are converted to the database server’s local time. The clock on the machine running Database Health Monitor plays no part, so a client in another time zone, or one whose clock has drifted, sees the same stuck items as anyone else.

Messages you may see

The queue is empty, which is the right answer. Nothing is waiting to be delivered, no events are unprocessed, no report is rendering and no subscription deletion is stuck. A report server spends most of its life like this. Press F5 while a subscription is due to watch one go through.

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

Frequently asked questions

The page is empty. Is something wrong? No. An empty queue is the normal state of a healthy report server. Deliveries only sit here for the moments between a subscription firing and the report being sent.

There are events but no deliveries. What does that mean? Work has been queued and the report server service has not turned it into deliveries. That points at the service itself rather than at an email or file share destination, and the line under the headline says so.

A delivery shows Attempt 3. What does that mean? It is on a retry rather than its first attempt, which usually means the destination refused it. Next try shows when the service will next pick it up, and Open Delivery History on the right click menu opens the delivery history, where the message from each attempt is recorded.