SSRS Snapshots and History

Overview

SSRS Snapshots and History lists the rendered copies of reports that a Reporting Services catalog is keeping, weighs each one, and says which of them are worth a second look.

A snapshot is a report rendered once and stored, so that opening it costs the data sources nothing and everybody sees the same numbers. Report history is the same thing kept on purpose, several versions deep. Both are useful, and both are where a report server database’s size tends to come from: a report definition is kilobytes, and its stored renderings can be far larger.

The page decides what each stored row is from what in the catalog points at it:

  • Current snapshot – the stored rendering a report currently runs from (Catalog.SnapshotDataID).
  • History – a rendering that one or more report history entries point at. A rendering that is both reads Current snapshot and history.
  • Orphaned – a rendering no report, history entry or report definition points at, and no open session is holding.
  • Held by a session – a rendering nothing in the catalog points at that an open report session still holds.

Compiled definitions are not renderings. Every published report and shared dataset keeps its compiled definition in the same table (Catalog.Intermediate), so on a report server that keeps no snapshots at all, every row in that table is one of these. The page leaves them off the chart and the grid and counts them in the line under the headline instead.

Every rendering is weighed rather than just counted, because a server with four enormous snapshots is not tidier than one with forty small ones.

Where to find it

Only shown for a database holding a Reporting Services catalog.

  • Tree: expand the report server database, then Real Time > SSRS > Snapshots and History.
  • SSRS Caching and Execution: the Snapshots toolbar button, or right click a report that runs from a snapshot and choose Open Snapshots and History.
  • SSRS What Fills This Database: the Snapshots toolbar button, or right click one of the rendering tables and choose Which renderings these are.

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.SnapshotData, dbo.History, dbo.Catalog, dbo.SegmentedChunk, dbo.ChunkSegmentMapping and dbo.ChunkData.
  • The query is given 180 seconds. If it does not finish, the page says so rather than drawing an empty grid that looks like an empty server.

The two views

The toolbar has a two part switch, By size and By age, and two buttons that open SSRS Caching and Execution and SSRS What Fills This Database.

By size

One bar per stored rendering, sorted largest first. The label is the report name, or (no report) when neither a report nor a history entry points at the rendering. The line under it is the kind and how long ago it was taken. The value on the right is the rendering’s size.

By age

The same renderings, sorted oldest first, with the bar length set by days since the rendering was taken and the value on the right saying how long ago that was.

Colors and outlines

Color Meaning
Green Nothing to say about this rendering
Amber Nothing points at it and it is past its expiration date
Red Orphaned: nothing points at it and no session holds it

An orphaned or expired rendering also gets an amber outline around its bar. Hover over a bar for the report path, kind, size, page count, when it was taken, when it expires and the notes.

An expiration date only counts on a rendering nothing points at. The report server’s cleanup only removes an expired rendering once nothing references it, so the date on a current snapshot or a history entry decides nothing and is not flagged.

The chart draws as many rows as fit under the page’s chart height limit. When there are more, the note under the chart says how many of how many are shown, and the grid still holds every one.

Reading the grid

Column What it holds
Report The report’s catalog path, or (no report or history entry) when nothing in the catalog claims it
Kind Current snapshot, History, Current snapshot and history, Orphaned or Held by a session
Taken When the rendering was created
Size The stored rendering’s size, from both storage formats added together
Pages The page count the report server recorded for the rendering
Expires The expiration date, or no expiry
History kept The report’s snapshot limit: a number of versions, server default, forever, or - when there is no report
Notes Everything the page found about this rendering, in words

The grid is sorted newest first. Kind and Notes are drawn in amber or red on a rendering that is expired or orphaned.

The Notes column can say:

  • Nothing points at this rendering and both reference counts are zero, so the report server’s own cleanup removes it on a later pass (one that stays says that cleanup is not running), with how much space it is holding.
  • Nothing points at this rendering yet its permanent reference count is above zero, so the report server’s own cleanup will never remove it, with how much space it is holding.
  • Nothing in the catalog points at it and an open report session still holds it.
  • It is past its expiration date.
  • The report keeps its history forever, so nothing will ever trim it.
  • It was rendered for one user’s parameters and cannot be reused by anybody else.

Findings

The headline counts the stored renderings, their total size, and how many are orphaned. The line under it adds, where they apply:

  • How much space the orphaned renderings hold.
  • How many renderings nothing points at are past their expiration date and waiting on the report server’s own cleanup.
  • How many belong to a report whose snapshot limit is unlimited.
  • How many were built for one user’s parameters.
  • The date of the oldest rendering.
  • How many more rows in the table are compiled definitions of published reports and shared datasets, and how much they take, since those are left off the page.
  • That sizes add the newer segment storage and the older chunk storage together.
  • That the page reads the catalog tables directly, so it covers every item on the report server rather than what the portal would show you.

Actions

  • Click a bar to select that rendering’s row in the grid.
  • Double click a bar to open its report in SSRS Catalog Inventory with the report’s row selected. A rendering with no report only has its row selected.
  • Right click a bar for the same menu as that rendering’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.

Nothing on this page deletes a rendering.

Where the data comes from

dbo.SnapshotData, one row per stored rendering or compiled definition, with what points at it found three ways:

  • through dbo.Catalog.SnapshotDataID, which is a report’s current rendering,
  • through dbo.History, which is a report’s history entries, and
  • through dbo.Catalog.Intermediate, which is a published report’s or shared dataset’s compiled definition. These rows are counted and left off the page.

A row found none of those ways is orphaned, unless its transient reference count says an open session still holds it.

The size is two numbers added together on the client side:

  • ChunkSegmentMapping.ActualByteCount summed over the rendering’s SegmentedChunk rows, which is where newer report servers keep the bytes. Summing the mapping rather than measuring the segment avoids charging a shared segment to every chunk that uses it.
  • DATALENGTH of ChunkData.Content, which is where older versions kept them. A report server upgraded in place can have renderings in both.

Both are subqueries rather than joins, so a rendering with several chunks is not multiplied into several times its real size.

Frequently asked questions

What does “forever” mean in the History kept column? The report’s own snapshot limit is minus one, so its history is never trimmed. Server default means the report has no limit of its own and uses SystemSnapshotLimit, which SSRS Server Configuration shows.

Why is the report shown as “(no report)”? The page found no report whose current rendering is this one, no history entry and no report definition pointing at it. That is how it tells a rendering that still belongs to something from one that does not.

The page says “This report server stores no rendered reports”. Is that a problem? No. Nothing is set to keep its result and no report keeps history, so every report is rendered fresh when somebody opens it. That is the simplest arrangement, and the one that costs the data sources the most. The message also says how many rows the snapshot table holds anyway: those are the compiled definitions every published report and shared dataset keeps there.