SSRS What Fills This Database

Overview

SSRS What Fills This Database answers where a report server database’s space has gone.

The usual guess is the report definitions, and it is usually wrong. What fills a report server is what it writes about its own work: the execution log, the stored renderings, the session data, and on a server whose service has been unwell, a queue it never drained.

The page sizes every table from the allocation metadata rather than by reading the tables, so it costs the same on a very large catalog as on an empty one. It reads both halves of a report server:

  • The catalog itself, the database the page was opened on.
  • The companion temp database, named as the installer names it: the catalog’s name with TempDB on the end. Session data lives there, and it is routinely larger than the catalog.

Where to find it

Only shown for a database holding a Reporting Services catalog.

The SSRS What Fills This Database report: tables ranked by reserved space above the grid
Tables from both report server databases ranked by the space they reserve, with the headline saying how much is in each database.

Requirements

  • A Reporting Services catalog database. The SSRS pages are offered when dbo.ExecutionLog and dbo.Catalog both exist.
  • Enough permission to see the tables in sys.tables, sys.indexes, sys.partitions and sys.allocation_units. When nothing comes back, the page says that sizing a database needs VIEW DATABASE STATE or membership of db_datareader.
  • The companion temp database is read on a separate connection to the same server. If it has another name, is not there, or the login cannot read it, the page carries on with the catalog alone and says that it is showing only half the picture.
  • dbo.ConfigurationInfo for the execution log retention.
  • Each query is given 60 seconds.

Reading the chart

The SSRS storage bars
One bar per table that has reserved any space, across both report server databases, largest first.

One bar per table with any reserved space, across both databases, largest first. Empty tables are left off the chart and kept in the grid. The label is the table name. The line under it is the database it is in and its row count. The value on the right is the reserved size.

Color Meaning
Red A size that is a finding: Event or Notifications holding more than 1,000 rows, or SubscriptionHistory reserving more than 100 MB
Amber A table reserving more than 50 MB and using less than half of it
Green Neither

Red bars also get an amber outline. Hover over a bar for the full database and table name, reserved and used sizes, the row count, and what the table holds.

The chart draws as many rows as fit under the page’s chart height limit, and the note under it says when there are more.

The page has no views to switch between. Its toolbar buttons open other pages: Snapshots (SSRS Snapshots and History), Queue (SSRS Delivery Queue), Configuration (SSRS Server Configuration) and Catalog (SSRS Catalog Inventory).

Reading the grid

The SSRS storage grid
One row per table in both databases, including the empty ones.
Column What it holds
Table The table name
Database The catalog or its companion temp database
Reserved Bytes the table has taken from the file, including its indexes
Used Bytes the table has data in
Rows Rows in the heap or clustered index, from the partition metadata
Share This table’s reserved space as a share of both databases together
What it holds A plain words description, for the tables where knowing changes what to do

Reserved is drawn in red or amber on a flagged table.

What it holds is filled in for a short list of tables and left blank for the rest:

Table What the page says
ExecutionLogStorage One row per render, trimmed by the report server’s own nightly cleanup, with the retention in days when it is known
Segment The bytes of stored renderings
ChunkData Stored renderings written by an older version of the report server
SegmentedChunk, ChunkSegmentMapping The index over the stored renderings rather than the renderings themselves
SnapshotData One row per stored rendering, with its expiry and reference counts
SessionData One row per open report session
Event The work queue, which should be nearly empty
Notifications The delivery queue, which should be nearly empty
Catalog The report definitions, which are rarely the large thing here
SubscriptionHistory One row per delivery attempt, kept to each subscription’s most recent attempts
PersistedStream Rendered output held for a session

The gap between Reserved and Used is what a large delete leaves behind. That space is reused by the same table and is not returned to the file, which is why a report server that trimmed its execution log does not get smaller.

Findings

The headline gives the space in the catalog, the space in the temp database when it could be read, and the largest table’s share of the total. The line under it adds, where they apply:

  • Each flagged queue (Event or Notifications), with its row count, saying it should be nearly empty and that SSRS Delivery Queue says what is in it.
  • A flagged SubscriptionHistory, with its size and row count, saying it is a record of past deliveries rather than a queue, so a large one means many subscriptions or long delivery messages rather than work waiting, and that SSRS Delivery History says what is in it.
  • That the companion temp database could not be read, so this is only half the picture.
  • How much space is reserved and not used, when that is over 100 MB.
  • How many days the execution log is set to keep.
  • That sizes and row counts come from the allocation metadata, so row counts can lag slightly after a bulk operation.
  • That the page reads the catalog tables directly.

Actions

  • Click a bar to select that table’s row in the grid.
  • Double click a bar to open the page that says what is in that table, the one its right click menu offers below (for example Snapshots for Segment, Delivery Queue for Notifications). A table with no such page just has its row selected.
  • Right click a bar for the same menu as that table’s grid row.
  • Right click a grid row, below the usual Copy, Copy with Headers and Select All, for:
  • Right click an empty part of the chart to copy the chart to the clipboard.

Where the data comes from

One query, run once in the catalog and once in the companion temp database:

  • sys.tables joined to sys.indexes, sys.partitions and sys.allocation_units, with total_pages and used_pages summed across every index and multiplied by 8,192 bytes.
  • The row count is a subquery over sys.partitions for index_id 0 and 1 only, the heap or the clustered index. Counting every index would report a table with three nonclustered indexes as having four times its rows. The page sums do not filter that way, because an index’s pages are space the table is using.

Nothing reads the rows of the tables themselves.

Frequently asked questions

Why is the temp database missing? The page looks for the catalog’s name with TempDB on the end, which is what the installer creates. A hand configured installation with another name, a database that is offline, or a login that can read the catalog but not the temp database all leave the page showing the catalog alone, and the line under the headline says so.

The execution log was trimmed but the database is no smaller. Why? Deleting rows frees space inside the table rather than returning it to the file. It shows up here as a gap between Reserved and Used, and the table will reuse it as it grows again.

Why is Event or Notifications red? Both are queues that should be close to empty. More than 1,000 rows usually means the report server service is not working through them. SSRS Delivery Queue says what is in them.

Why is SubscriptionHistory red? It reserves more than 100 MB. That is not a backlog: the table is a record of past delivery attempts, and the report server keeps only each subscription’s most recent ones, so a large table means a great many subscriptions or long delivery messages. SSRS Delivery History says what is in it.