Temporal Tables
Overview
A system-versioned temporal table keeps every old version of every row in its history table. Those history tables quietly grow to many times the size of the table they version, usually with no retention policy and often as a heap.
The Temporal Tables report lists every system-versioned table in the database and shows:
- a chart of history size by table, with the current table’s size drawn as a thinner bar under each one so the ratio is visible at a glance,
- a grid with the rows and sizes of both tables, the history to current ratio, and a Findings column,
- a script pane with the findings for the selected table and the script that addresses them.
A database with no system-versioned tables says so in one line: “No system-versioned tables”.
Where to find it
| Route | How |
|---|---|
| Database tree | Expand a database → Real Time → Temporal Tables |
The page title reads Temporal Tables for <database name>. The item is shown on SQL Server 2016 and later, and not for tempdb.
The toolbar
| Control | What it does |
|---|---|
| Refresh | Reads the database again |
| Check Oldest Row | Reads the oldest version in the selected table’s history (enabled when a row is selected) |
| Cancel | Stops a read in progress (shown only while reading) |
Every read runs in the background on its own connection with the progress bar. The page read times out after 120 seconds.
Check Oldest Row reads MIN of the period end column from the history table. That reads the table (a seek when an index leads on the period end column, otherwise a scan), so it is only run when you ask, and it times out after 300 seconds. The result is shown in the Oldest Version column and the toolbar, and is kept across a Refresh for tables that are still there.
The findings
| Finding | Level | When |
|---|---|---|
| No retention policy | Warning | HISTORY_RETENTION_PERIOD is INFINITE, so every version is kept for ever |
| Retention disabled at database level | Warning | The table has a retention period, but TEMPORAL_HISTORY_RETENTION is OFF for the database, so nothing is cleaned up |
| History is heap | Warning | A heap history table has no order on the period columns, so FOR SYSTEM_TIME queries scan it and retention cannot be set |
| History more than 10x current | Warning | The history table’s reserved size is more than 10 times the current table’s |
| Database retention not checked | Information | The table has a retention period, but TEMPORAL_HISTORY_RETENTION could not be read for the database |
| Retention requires manual cleanup | Information | SQL Server 2016 has no HISTORY_RETENTION_PERIOD; old versions stay until a job deletes them. Replaces the retention findings on 2016 |
| History table not visible | Information | The login cannot see the history table, so its size and index are not known |
A table with no findings shows OK. The heading counts the tables with a warning, for example “4 system-versioned tables in Sales: 2 need a look”, or “no findings”.
Reading the chart
Each table is one row, largest history first, up to 20 tables. The history bar is blue and the current table’s bar under it is green; a table with a warning draws its history bar in orange. The label after the bars gives the history size and the ratio, for example “1.2 GB (14.3x)”.
Hover a bar for both sizes, both row counts and the findings. Click a bar to select that table in the grid and open its script.
The note under the heading gives the time of the read and the SQL Server version, says when the version has no HISTORY_RETENTION_PERIOD, when TEMPORAL_HISTORY_RETENTION is OFF for the database or could not be read, and reminds you that sizes are reserved space and the ratio is history over current.
Reading the grid
| Column | What it is |
|---|---|
| Table | The system-versioned table |
| History Table | Its history table |
| Current Rows / Current MB | Rows and reserved size of the current table |
| History Rows / History MB | Rows and reserved size of the history table |
| History to Current | History reserved size over current reserved size, for example 12.5x; N/A when the current table is empty |
| Findings | The findings, or OK. Dark orange for a warning, gray for information only |
| Retention | The retention period (for example 6 Months), Infinite, or Manual cleanup on SQL Server 2016 |
| History Index | Heap, Clustered columnstore, Clustered on <period end column>, or Clustered (not on <period end column>) |
| Compression | The history table’s data compression, or a range such as NONE to PAGE when its partitions differ |
| Nonclustered | The number of nonclustered indexes on the history table |
| Oldest Version | Empty until Check Oldest Row is run; then the oldest period end in UTC with how many days ago it was, Empty, or Could not read |
Hover a row for the period columns and the full findings. Right-click a row for Show Script for <table>, Check Oldest Row and Copy the findings as text. The grid exports to CSV and Excel like every other grid.
The script pane
The pane on the right shows the findings for the selected table and a script for them. It opens on the first table with a warning (or the largest history) when the page is first shown, and follows the selection. Copy script copies it, Open in window shows it in a script window, and Close hides the pane until you choose Show Script or click a bar.
The script is shown, never run by the report. It holds, as needed:
- a clustered columnstore index on the history table when it is a heap or is not clustered on the period end column (retention needs one or the other). A clustered rowstore index is replaced in place with DROP_EXISTING. When the history is already clustered on the period end column, the conversion is offered as a comment,
ALTER DATABASE CURRENT SET TEMPORAL_HISTORY_RETENTION ONwhen it is off,ALTER TABLE ... SET (SYSTEM_VERSIONING = ON (HISTORY_RETENTION_PERIOD = ...)), with 6 MONTHS as a starting point when no retention is set (pick the retention the business needs), or the current period restated.
On SQL Server 2016 it is a manual cleanup template instead: in one transaction, switch versioning off, delete the versions whose period ended more than 6 months ago, and switch versioning back on with the same history table, to schedule as an Agent job.
Permissions and versions
- Temporal tables arrived in SQL Server 2016; on an older instance the page says so. HISTORY_RETENTION_PERIOD and TEMPORAL_HISTORY_RETENTION arrived in SQL Server 2017, so on 2016 the retention findings are replaced with a note that cleanup is manual.
- Rows and sizes come from
sys.partitionsandsys.allocation_units, so VIEW DATABASE STATE is not needed. A history table the login cannot see is listed with no sizes. - TEMPORAL_HISTORY_RETENTION is read from
sys.databases; if that is refused, the page says the database retention was not checked.