SSRS Never Run

Overview

Every report server accumulates dead weight. A project ships forty reports, six get used, and the other thirty four stay in the catalog forever because nobody can prove they are safe to remove. SSRS Never Run is that proof, or as close to it as the execution log allows.

It lists three kinds of item, which want different treatment:

Why it is here What it means
never run A report, linked report, mobile report, Power BI report or Excel workbook with no row in the execution log. Nobody has opened it inside the log’s retention window, including its own subscriptions
nothing points at it A shared data source that no data source row links to, or a shared dataset that no report references. This is a reference count rather than a run count, so it does not depend on the retention window
empty folder A folder, other than the root, with nothing in it

The report server’s own System Resources folder, which it keeps under a GUID name for things such as portal branding, is never listed, and nothing inside it is either.

Nothing on this page deletes anything. It gives you the list; the decision about whether a quarterly report is dead belongs to somebody who knows the business.

The retention window is the whole caveat

The execution log keeps a fixed number of days, 60 by default. “Never run” on this page means never run inside that window. A report run once a quarter looks abandoned on a server keeping sixty days, which is why the window is the first thing the line under the headline says.

A report with subscriptions attached is also treated differently. Its subscriptions have not run it inside the window either, so the page reads it as switched off rather than abandoned, draws it green rather than red, and points at SSRS Subscriptions to see whether they are disabled.

Where to find it

This report is only shown for a database that holds a Reporting Services catalog.

  • Tree: expand the report server database, then Real Time > SSRS > Never Run.
  • Toolbar: the Never run button on SSRS Catalog Inventory and SSRS Shared Datasets.
  • Right click: Look for it on Never Run on a runnable item’s row in Catalog Inventory, which opens this page with that item selected when it is listed. When it is not listed, it has run inside the retention window, and nothing is selected.
The SSRS Never Run report: unused items ranked by how long since they were last changed
Items nobody runs or references, the longest untouched first, above one grid row per item.

Requirements

  • A Reporting Services catalog database, normally called ReportServer. The SSRS pages are offered when dbo.ExecutionLog and dbo.Catalog both exist in the database being looked at.
  • SELECT on dbo.Catalog, dbo.Users, dbo.ExecutionLogStorage, dbo.Subscriptions, dbo.History and dbo.DataSource, and on dbo.DataSets where the catalog has it. On a catalog without a DataSets table, shared datasets are not checked for references, and the line under the headline says so.
  • SELECT on dbo.ConfigurationInfo to read how many days the execution log keeps. Without it the page still works, and the line under the headline says the window could not be read and that the default is 60 days.
  • Newer report servers keep each item’s size in Catalog.ContentSize. Where that column is missing, the size is computed with DATALENGTH(Content) instead.

Reading the chart

The SSRS never run chart
One bar per item, longest for the item that has gone longest without a change.

One bar per item, ranked by the days since it was last changed, or since it was published when there is no change date. The line under each name is its type and why it is on the list, and the text on the right reads as “14 months ago” or “yesterday”. An item with neither date sorts to the top as unknown with no bar, so it does not stretch the scale for every other item.

Color Meaning
Green The item still has subscriptions attached, so it is switched off rather than abandoned
Amber An empty folder, or an item last changed within the past year
Red An item with no subscriptions that has not been changed in over a year

The red rows also get an amber outline around the bar. They are the strongest candidates for removal on the page.

The chart draws at most the first thirteen rows. When there are more, the note under the chart says how many are shown out of how many, and the grid holds them all. Hover over a bar for the item’s path, its type and reason, its size, when and by whom it was published, and its notes.

Reading the grid

The SSRS never run grid
One row per item, never run reports first, then unreferenced shared items, then empty folders.
Column What it holds
Path The item’s catalog path
Type Report, Linked report, Data source, Shared dataset, Folder, and so on
Why it is here never run, nothing points at it or empty folder
Last changed The date the item was last modified
Published The date the item was created
Published by The account that created it
Size Bytes of content, shown and exported with a unit such as KB. The cell holds the plain byte count underneath, so the column sorts by size
Notes What the page found about the item

The grid has no run count or last run column: every item listed either has no row in the execution log or is a kind of item the log never records, so those columns could only say 0 and never.

The grid lists the never run items first, then the unreferenced shared data sources and shared datasets, then the empty folders, each group in path order. Why it is here is drawn amber or red on the chart’s color rules, and Notes is drawn in the row’s color whenever it has anything to say.

What the notes say

  • Subscriptions are still attached, so this is switched off rather than abandoned, and the Subscriptions page says whether they are disabled.
  • Kept snapshots go with it, which is where its real size is. The count comes from the report server’s History table.
  • When it was last changed, for an item untouched for over a year.
  • For an unreferenced shared data source or shared dataset, that it is safe to remove once nothing is mid publish, because a reference count is a complete answer where a run count is not.

Findings

The headline counts the reports that have never run, the shared items that are unreferenced and the empty folders, and what share of everything runnable on the server the never run reports are. The line under it adds:

  • Always first: the retention window “never run” is measured against, in days, or that the window could not be read.
  • How many of the listed items still have subscriptions attached.
  • How many bytes of definitions sit behind the list, and that this is not usually where a report server’s size is.
  • That an unreferenced shared item is a complete answer rather than a windowed one.
  • That shared datasets were not checked for references, on a catalog with no DataSets table.
  • That the page reads the catalog tables directly, so it shows every item on the report server rather than the subset the portal would show you.

Actions

  • Click a bar to select that item’s row in the grid.
  • Double click a bar to open SSRS Catalog Inventory with that item selected, as This item in the catalog does.
  • Right click a bar, or one or more selected grid rows, for:
    • Copy the path, or Copy the N selected paths with several rows selected, which puts every selected path on the clipboard one per line
    • This item in the catalog, which opens SSRS Catalog Inventory with the first selected item selected
    • All subscriptions on this server, when the first selected item has subscriptions. It opens SSRS Subscriptions for the whole report server, not filtered to this item
    • What fills this database
  • Right click the chart away from a bar for Copy Chart to Clipboard.
  • The toolbar’s Catalog, Storage and Subscriptions buttons open SSRS Catalog Inventory, SSRS What Fills This Database and SSRS Subscriptions.

Where the data comes from

Four result sets in one batch:

  1. Never run. dbo.Catalog items of types 2, 4, 12, 13 and 14 (report, linked report, mobile report, Power BI report and Excel workbook) with no row in dbo.ExecutionLogStorage, found with NOT EXISTS, which can stop at the first log row rather than counting the whole log. Each one also carries its subscription count from dbo.Subscriptions and its kept snapshot count from dbo.History.
  2. Nothing points at it. Shared data sources (type 5) with no dbo.DataSource row whose Link names them, and shared datasets (type 8) with no dbo.DataSets row whose LinkID names them. The dataset half is left out on a catalog without a DataSets table.
  3. Empty folders. Folders (type 1) with a parent, so not the root, and no catalog item whose parent they are.
  4. The count of every runnable item, for the share in the headline.

Every one of the four leaves out the report server’s System Resources folder, catalog path /68f0607b-9378-4bbb-9e70-4da3d7d66838, and everything under that path. The report server hides it the same way in its own browse procedures: by that fixed path, not by the Hidden column, which is NULL on it. It is usually empty, so without that filter it would always appear as an empty folder.

The run test reads dbo.ExecutionLogStorage rather than one of the ExecutionLog views. The page only needs to know whether any row exists for an item, the storage table carries the item id directly, and it is the same across versions where the views are not.

The retention window comes from the ExecutionLogDaysKept row of dbo.ConfigurationInfo.

The batch runs at READ UNCOMMITTED, so reading the catalog does not take shared locks that block the report server writing to it.

Messages you may see

Everything on this report server is in use. Every report has run inside the retention window, every shared data source and shared dataset has something pointing at it, and no folder is empty.

The report server query did not finish in time. The query waits up to three minutes. The execution log can hold millions of rows on a busy report server. Press F5 to try again.

Frequently asked questions

A report on this list is run every quarter. Why is it here? Because “never run” means never run inside the execution log’s retention window, 60 days by default. The line under the headline says what this server’s window is. A report run less often than that looks abandoned here and is not.

Why is a never run report drawn green? It still has subscriptions attached. A report whose subscriptions exist but have not run it is switched off, not forgotten, so the page does not rank it with the reports nobody has touched.

Does the page list unused shared datasets? Yes. A shared dataset that no report references is listed as nothing points at it, next to the unreferenced shared data sources. The catalog needs a DataSets table for that check; without one the line under the headline says shared datasets were not checked. SSRS Shared Datasets shows the same datasets as unused, along with the ones that are used.