SSRS Delivery History

Overview

SSRS Delivery History shows what actually happened each time a subscription tried to deliver.

SSRS Subscriptions can only show a subscription’s last status, which is one string overwritten on every run. A subscription that failed on Tuesday and succeeded on Wednesday looks perfect there, and the Tuesday nobody got the report is invisible. This page reads the history the report server keeps beside it, so an intermittent failure reads as a pattern.

Two catalog tables answer two different questions:

  • SubscriptionHistory is one row per attempt, with a start, an end and the message the delivery extension wrote. That answers how often a subscription fails and how long it takes.
  • SubscriptionResults is one row per recipient on a data driven subscription. A data driven subscription that reports success has succeeded at generating its list; whether one address on it failed is recorded here and nowhere else.

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 History.
  • Subscriptions: press Delivery history on the toolbar of SSRS Subscriptions, or choose Open Delivery History from its right click menu.
  • Delivery Queue: press Delivery history on the toolbar of SSRS Delivery Queue, or choose Open Delivery History from its right click menu.

Requirements

  • A Reporting Services catalog database. The SSRS pages are offered when dbo.ExecutionLog and dbo.Catalog both exist.
  • dbo.SubscriptionHistory and dbo.SubscriptionResults, which arrived with SQL Server 2016 Reporting Services. The page checks for both before it queries, and on an older catalog shows This report server does not keep a history of its subscription deliveries. rather than an error.
  • SELECT on dbo.SubscriptionHistory, dbo.SubscriptionResults, dbo.Subscriptions and dbo.Catalog.

The two views

The toolbar switches between By failures and By duration. Both rank subscriptions, not individual attempts.

By failures

Subscriptions with the most failed attempts first. The value on the right reads 3 of 10, or none for a subscription whose attempts all succeeded.

By duration

Subscriptions by average time per delivery attempt, slowest first.

Reading the bars

Each bar is labeled with the report name, with the subscription’s description underneath, or its delivery method when it has no description.

Mark Meaning
Green bar No attempt failed
Amber bar Some attempts failed
Red bar Every attempt failed
Amber outline At least one attempt failed

Hover over a bar for the number of attempts, the share that failed, the average time and when it was last tried.

Reading the grid

Column What it holds
Report The report’s catalog path, or (report deleted)
Outcome Succeeded, Aborted or Failed
Started When the attempt started
Took End minus start, running when there is a start and no end, or –
Recipients On a data driven subscription’s newest attempt, the recipient count, or 2 of 40 failed
Message The message the delivery extension wrote, colored when the outcome is not a success
Status The raw Status value from SubscriptionHistory
Type The raw Type value from SubscriptionHistory
Description The subscription’s description

How the outcome is decided

SubscriptionHistory.Status is a number whose meaning Microsoft does not document, so the page does not decode it. It is shown raw in the Status column, and the Outcome is read from the message text instead:

  • An empty message, or one starting New Subscription, is Aborted, drawn in amber.
  • Otherwise, a message containing Failure, Failed, Error or Unable is Failed.
  • Anything else is Succeeded.
  • An attempt that carries recipient failures is Failed, whatever its message says.

Findings

The headline counts the delivery attempts and the subscriptions they belong to, and the share that failed, or says none failed. The line under it adds, where they apply:

  • That the page is showing the newest 2,000 attempts rather than everything the server has kept.
  • The dates the attempts on the page cover.
  • How many attempts have a start and no end, which is either a delivery running now or one the service was stopped part way through.
  • How many individual recipients failed on the newest run of a data driven subscription, which the run’s own status does not report.
  • That recipient counts describe each subscription’s newest run rather than the row they sit on.
  • That the outcome is read from the message text.

Actions

  • Click a bar to select that subscription’s newest attempt in the grid.
  • Double click a bar to select every attempt of that subscription in the grid, with the newest scrolled into view.
  • Right click a bar for the grid’s right click menu on that subscription’s newest attempt.
  • Right click a row for Copy the delivery details, Copy the message, Copy report path, Open Subscriptions (opens SSRS Subscriptions) and Open Delivery Queue (opens SSRS Delivery Queue). Both open for the whole server; neither filters to the attempt’s subscription.
  • Right click an empty part of the chart to copy the chart to the clipboard.
  • The toolbar buttons Subscriptions and Queue open those pages.

Where the data comes from

The newest 2,000 rows of dbo.SubscriptionHistory by start time, joined to dbo.Subscriptions and dbo.Catalog. The first 1,000 characters of each attempt’s Details are read for the copy menu item.

A second read groups dbo.SubscriptionResults by subscription, counting recipients and the results whose text contains ailure, rror or nable to. SubscriptionResults keeps no timestamp, so those counts cannot be attributed to a particular attempt; they are attached to each subscription’s newest attempt on the page and nowhere else.

The report server trims SubscriptionHistory itself as it writes to it. On the SSRS 2019 catalog, the procedure that adds an attempt keeps only the ten newest for that subscription, so on that version this page shows at most ten attempts per subscription rather than a long history.

Messages you may see

No subscription has run on this report server. The history table exists and is empty. Either nothing is scheduled, or nothing scheduled has fired yet. SSRS Subscriptions says which.

This report server does not keep a history of its subscription deliveries. The catalog is older than SQL Server 2016 Reporting Services and does not have the tables this page reads. Every other page in the SSRS list works on it.

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

Frequently asked questions

Why is the Status column a bare number? Because its encoding is undocumented. Guessing a decode and being wrong would be worse than showing the value, so the page shows it raw and decides the outcome from the message text.

Why does only one row per subscription show a recipient count? SubscriptionResults has no timestamp, so it can only describe a subscription’s latest run. The page puts the count on the newest attempt rather than implying every historic attempt had the same result.

Why do I only see a few attempts for a subscription that runs every hour? The report server keeps a short history per subscription and deletes older attempts as new ones are written. Use SSRS Failed Executions for the report runs over the full execution log retention.