Key Exhaustion by Database
Overview
The database level Key Exhaustion report answers the question for one database at a time. On an instance with a hundred databases that means a hundred visits to find the one int identity at 94 percent.
The Key Exhaustion by Database report runs the same identity column and sequence queries in every database on the instance and shows:
- the 50 worst keys across the whole instance, ranked by verdict and then by percent used, so a key projected to run out soon is listed even when its percent is low,
- a fixed 0–100 runway gauge for each of them, with ticks at 70% and 90%,
- verdict totals counted over every key read, not only the 50 listed,
- the databases that could not be read, with the reason for each.
The percent, the verdict and the projection are worked out by the same code as the database page, so the two pages cannot disagree about a key.
Where to find it
| Route | How |
|---|---|
| Server tree | Right-click the server → Instance Level Reports → Key Exhaustion by Database |
| Instance reports navigator | Storage group |
| Report arrows | Previous is Job Schedules, next is Large Tables |
Double-click a row, in the chart or the grid, to open the Key Exhaustion page of that database. A sequence row opens it on the Sequences view.
The toolbar
| Control | What it does |
|---|---|
| Refresh | Reads every database again |
| All / Watch / Critical | Narrows the chart and the grid to one band of the keys listed, without reading again. Hidden until there are keys to show |
| Cancel | Stops a read in progress (shown only while reading) |
The read walks every database, so it runs in the background with the progress bar. The status text beside the buttons says “Reading every database…” while it runs.
The verdicts
| Verdict | When |
|---|---|
| Critical (chip reads Runs out soon) | 90% or more of the range used, or projected to run out within 90 days |
| Watch | 70% or more of the range used, a CYCLE sequence that feeds a key (at any percent), or projected to run out within a year |
| (none, healthy) | Everything else |
A CYCLE sequence behind a key does not stop when it reaches the top: it wraps back to its minimum, and the next insert reuses a number a row already holds and fails with a duplicate key error. That is why it is a finding at any percent. Its chip reads Cycles into a key. A key raised by its projection has a chip that reads Out in N days.
A column feeds a key when it is a key column of a primary key, a unique constraint or a unique index.
Reading the chart
The headline gives the verdict over every key read, for example 3 keys are nearly out of numbers – 5 more worth watching, and adds how many CYCLE sequences feed a key. With nothing close it reads No identity columns or sequences are close to running out.
The line under it counts the identity columns and sequences, the databases read and the databases that could not be read, says when only the worst 50 are listed, how many keys are projected to run out within a year, and which filter is on.
The zone strip shows how many keys are healthy, watch (70-90%) and critical (over 90%).
The bars draw the first 15 rows of the grid. Each is named database – schema.object (with the column for an identity), and carries a verdict chip (none on a healthy row) and a data type chip. The detail line says whether it is an identity or a sequence and, for a sequence, CYCLE, an increment other than 1 and how many columns it feeds. The fill is green under 70%, amber from 70% and red from 90% or from the verdict. On the right is the last value and how many inserts (or values, for a sequence) are left of the whole range.
The footer says how many more rows are in the grid below and names the databases that were not read (the first four, then “and N more”).
Hover a bar for the full name, the type, the percent, the values left and the error that will stop it (8115 for an identity, 11728 for a NO CYCLE sequence), plus the growth rate when there is history. Click a bar to select its grid row.
Reading the grid
| Column | What it is |
|---|---|
| Percent | Range used, with the same gauge the chart draws, colored by verdict |
| Database | The database |
| Kind | Identity or Sequence |
| Name | schema.table.column for an identity, schema.sequence for a sequence |
| Type | tinyint, smallint, int, bigint, or decimal(p,0) / numeric(p,0) |
| Last Value | The last identity value, or the current value of a sequence; never used when nothing has been handed out |
| Limit | The value the increment is heading for: the top of the range, or the bottom for a negative increment |
| Remaining | How many more inserts or values fit; exhausted for a sequence SQL Server has marked exhausted |
| Projected | The date the key is projected to run out (see below). Empty without history |
| Notes | CYCLE sequence feeds a key, exhausted, never used, and for other sequences CYCLE, feeds N columns or no column default uses it |
Hover a row for the same tooltip as its bar. Selecting a row selects its bar. The grid exports to CSV and Excel like every other grid, with the numbers as they are drawn.
Right-click actions
| Item | What it does |
|---|---|
| Open Key Exhaustion for <database> | Opens the database page, where the scripts and every key of that database are |
| Copy sequence headroom check script | Sequence rows only. A read-only look at the sequence and the defaults that call it, with the ways out written as comments |
| Copy name | [database].[schema].[object], with the column for an identity |
Nothing on this page changes anything in a database.
Projected run-out date
When historic monitoring is installed and [DBHealthHistory] has the KeyExhaustionHistory table (filled once a day by trackKeyExhaustion), the growth of each key over the last 30 days gives a projected run-out date:
days left = values left ÷ values used per day
The projection needs at least 7 days of history, counting the first and last capture days. The Projected column is empty when there is less, when there is no history, when the key has never been used or is exhausted, or when the key is not moving toward its limit. A date more than 100 years away reads 100+ years. If the history cannot be read, every key is shown without a projection and nothing else changes.
What is read, and what is skipped
- Every database except tempdb and model (tempdb’s tables are temporary, and model’s are copied into every new database, where they are counted anyway).
- A database is skipped and named on the page when it is not online (with its state), when your login has no access to it, or when the read inside it fails (with the error). A database that fails part way is left out entirely rather than half counted.
- Identity columns come from
sys.identity_columnson user tables. Sequences come fromsys.sequences, and the columns they feed fromsys.default_constraints(defaults that call NEXT VALUE FOR), matched throughsys.sql_expression_dependenciesor by the [schema].[sequence] name in the default’s text. - If no database had any identity column or sequence, the page says There are no identity columns or sequences in the databases that could be read, with how many databases were read.
Permissions and versions
- Catalog views only, so the report is not gated. What a login sees in each database follows its metadata visibility.
- Where a login may not read
sys.sql_expression_dependencies(a guest in master and msdb, for example), a sequence is tied to a column default only when the default names its schema. - Sequences arrived in SQL Server 2012. On an older instance only identity columns are read, and the page says so.
- Before SQL Server 2017
sys.sequenceshas nolast_used_value, so a sequence cannot be marked never used there. - The read can take up to 10 minutes on a large instance before it times out.
- The projected dates need
DBHealthHistoryon the instance, open to your login, at a version that hasKeyExhaustionHistory.