Identity Column Usage

Overview

The Key Exhaustion report answers one question with a hard deadline attached: how much runway does each identity column and each sequence have left before it hits the top of its range and inserts start failing, with error 8115 for an identity column or error 11728 for a sequence.

This report was called Identity Column Usage until it gained a Sequences view. It has two views on the toolbar: Identity columns and Sequences.

Every key is drawn as a fixed 0–100 runway gauge with threshold ticks. The fixed scale is the point. Percent-of-range is naturally bounded, and the chart that should have a fixed scale is exactly this one.

The old report scaled the longest bar to full width no matter what, so a database whose worst column had used two percent of its range looked exactly as alarming as one about to run dry.

What else this fixes

• Its query joined sys.types on system_type_id, which fans out. A user-defined alias type over int matched both the alias row and the base row, and duplicated the table in the report.
• decimal and numeric identity columns fell out of the type test and vanished entirely. So did every never-used identity column, filtered away because its percentage was null.
• A negative increment counts toward the type’s minimum, not its maximum. The old arithmetic reported those as negative or nonsense percentages.
• Sequences were never read. An int or smallint sequence near its maximum, or a CYCLE sequence feeding a key that would wrap into duplicate keys, was missed.

---

Where to find it

Route	How
Database tree	Expand a database → Real Time → Key Exhaustion
Key Exhaustion by Database	Double-click a row, or right-click it → Open Key Exhaustion for <database>. A sequence row opens the Sequences view

The page title reads Key Exhaustion for <database name>.

---

The toolbar

Button	What it does
Identity columns · Sequences	Switches between the two views. Switching sets the filter back to All.
All · Watch · Critical	Narrows chart and grid together, without re-querying. A band with nothing in it is disabled.
Refresh	Reload now.

The chart draws the worst 15 keys of the view; the grid carries everything.

---

The verdicts

Verdict	Chip	When
Critical	Runs out soon	90% or more of the range used
Watch	Watch	70% or more of the range used
(none)		Below 70%

Verdict chips appear at those two thresholds, and the line under the headline names the worst offender in plain words rather than making you find it.

Two things raise the verdict whatever the percent:

• A CYCLE sequence that feeds a key is at least Watch, with the chip Cycles into a key. It does not stop at the top of its range; it wraps back to its minimum, and the next insert reuses a number a row already holds and fails with a duplicate key error. A column is a key when it is a key column of a primary key, a unique constraint or a unique index.
• A projected run-out date (see below), with the chip Out in N days.

Seventy percent sounds early. It is not: migrating an int identity to bigint on a large table is a project with an outage in it, and the time to start is while you still have thirty percent of the range.

---

Identity columns

Reading the chart

The headline reads, for example, 2 identity columns are nearly out of numbers – 3 more worth watching. The line under it names the worst column and how much of its type it has used, and counts the identity columns in the database and the sequences under Sequences. The zone strip shows how many columns are healthy, watch (70-90%) and critical (over 90%).

Each bar shows the column, its verdict chip and its type chip, the seed and increment when they are not (1, 1), the last value, and how many inserts are left of the whole range. The fill is green under 70%, amber from 70% and red from 90%. Hover a bar for the details; click it to select its grid row.

Reading the grid

Column	What it is
Percent	Range consumed. Carries the same gauge the chart draws.
Schema	Schema name.
Table	Table name.
Column	The identity column.
Type	int, bigint, smallint, tinyint, or decimal(p,0) / numeric(p,0).
Last Value	The current identity value, or never used.
Max Value	The top of the type’s range – or the bottom, for a negative increment.
Remaining	The headroom the arithmetic implies.
Projected	The date the column is projected to run out, from its growth over the last 30 days. Empty without history.

Remaining is the number to act on. A percentage is a ratio; the count of values left is what you compare against your insert rate.

Never-used identity columns are included rather than filtered out. A table with an identity column and no rows is a fact worth having.

A database with no identity columns says so, and if it has sequences it points you to the Sequences button.

---

Sequences

The Sequences view does the same for every sequence in the database (sys.sequences, SQL Server 2012 and later).

Percent used is the distance from the minimum to the current value over the whole range, counting up, and the mirror of that for a negative increment. The start value is not the base, because ALTER SEQUENCE ... RESTART WITH rewrites it. A sequence SQL Server marks exhausted (NO CYCLE, top reached) is 100%.

The headline reads, for example, 1 sequence is nearly out of numbers, and adds how many CYCLE sequences feed a key. Each bar’s detail line shows CYCLE, an increment other than 1, and how many columns the sequence feeds ((a key) when one of them is a key).

Column	What it is
Percent	Range consumed, with the same gauge.
Schema	Schema name.
Sequence	The sequence.
Type	The sequence’s data type.
Current Value	The current value, or never used.
Limit	The maximum value – or the minimum, for a negative increment.
Remaining	How many more values fit, or exhausted.
Projected	The projected run-out date. Empty without history.
Cycle	CYCLE or no.
Cache	The cache size, default, or none.
Used By	Each [schema].[table].[column] whose default calls NEXT VALUE FOR this sequence, marked (key) when the column is a key.

Hover a row for the full name, type, increment, CYCLE or NO CYCLE, range, values left and the error that will stop it: error 11728 for a NO CYCLE sequence, or a wrap back to its minimum for a CYCLE one.

A sequence marked never used (SQL Server 2017 and later, where last_used_value is NULL) keeps its percent rather than showing zero, because RESTART WITH also clears that value; a sequence restarted near its maximum is still flagged.

The view says so when there are no sequences, when the instance is older than SQL Server 2012, or when the sequences could not be read (the identity columns still show).

When your login may not read sys.sql_expression_dependencies, Used By finds only the defaults that name the sequence’s schema, and the line under the headline says so.

---

Projected run-out date

When historic monitoring is installed, the [DBHealthHistory] database records the current value of every identity column and sequence once a day (the trackKeyExhaustion procedure, run by the 15 minute collection). With a week or more of that history, the report works out how fast each key has grown over the last 30 days and projects the date it runs out:

projected date = today + values left ÷ values used per day

For example, an int identity growing by 1,000,000 a day with 50,000,000 values left is projected to run out 50 days from today.

The projection also raises the verdict, whatever the percent:

Projected to run out	Verdict
Within 90 days	Critical (the chip reads Out in N days)
Within a year	Watch

So a column at 20% that is growing fast enough to run out next quarter is flagged, and is ranked with the other critical keys. The bar tooltip shows the rate and the date. A date more than 100 years away reads 100+ years.

The Projected column is empty when there is no history, when there is less than a week of it, or when the key is not moving toward its limit (no inserts, or a reseed). Without history nothing else on the page changes. The same column is on both views and on the instance level Key Exhaustion by Database report.

---

Right-click actions

Nothing on this page runs anything against the database. Fixing a nearly-full identity column is a migration, not a click.

On the Identity columns view:

Item	What it does
Copy identity headroom check script	A query you can schedule to watch the column yourself. DBCC CHECKIDENT is scripted with NORESEED, so it changes nothing.
Copy bigint migration checklist script	Offered on tinyint, smallint and int columns – the steps, in order, with the parts that need an outage marked.
Copy column name	Just the name.

On the Sequences view:

Item	What it does
Copy sequence headroom check script	Reads the sequence and the defaults that call it, with the ways out as comments: raise MAXVALUE, move to a new bigint sequence (ALTER SEQUENCE cannot change the type), and for a CYCLE sequence, NO CYCLE.
Copy sequence name	Just the name.

---

How to read the report

1. Filter to Critical. If anything is there, it has a deadline.
2. Read Remaining, not just Percent. Ten percent of an int is still two hundred million values; ten percent of a smallint is three thousand.
3. Compare Remaining against your insert rate. That gives you a date, which is the only form of this number anybody can plan around. With historic monitoring installed, the Projected column does this for you.
4. Check the Watch rows too. Seventy percent is when a migration is comfortable rather than urgent.
5. Switch to Sequences. A sequence behind a key runs out just like an identity column.
6. Copy the migration checklist for anything critical and read it before scheduling.

To check every database on the instance at once, use Key Exhaustion by Database.

Common patterns

An int identity above 90% on a busy table. The classic. Plan the bigint migration now – it needs an outage on a large table.

A smallint or tinyint identity at any high percentage. These run out fast. Often a lookup table that grew beyond what its designer expected.

A negative-increment column reported correctly. Counting down toward the type’s minimum. The old report showed these as negative percentages.

A decimal or numeric identity appearing for the first time. It was always there; the old report dropped it.

A column at 100% that is still accepting inserts. Check for reseeding. Somebody may be resetting the identity, which works until it collides with existing values.

A CYCLE sequence feeding a key, at a low percent. Flagged as Watch on purpose. When it wraps, inserts fail with duplicate keys rather than stopping cleanly. Unless reuse is intended, it should be NO CYCLE.

A sequence with no column default using it. It may be called from code with NEXT VALUE FOR rather than from a default, so Used By is empty. It still runs out the same way.

---

Where the data comes from

sys.identity_columns joined to sys.types on the user type id rather than the system type id, so alias types do not duplicate rows. The sequences come from sys.sequences, and Used By from sys.default_constraints, tied to the sequence through sys.sql_expression_dependencies or by the [schema].[sequence] name in the default’s text, with sys.index_columns and sys.indexes for whether the column is a key. The page itself stores nothing. The projected dates read [DBHealthHistory].[dbo].[KeyExhaustionHistory], which historic monitoring fills once a day and trims with the rest of the history (never below 35 days).

---

Frequently asked questions

Why is the gauge fixed at 0–100 instead of scaling to the largest bar? Because percent-of-range is naturally bounded, and a relative scale made a database at two percent look identical to one about to fail.

Why does the report show columns at 0%? Because a never-used identity column is a fact worth having. The old report filtered them away.

Why is 70% a warning when I have millions of values left? Because the fix is a migration with an outage in it. Read the Remaining column against your insert rate to turn the percentage into a date.

What is error 8115? The arithmetic overflow SQL Server raises when an identity value cannot be incremented within its type. It stops inserts.

What is error 11728? The error NEXT VALUE FOR raises when a NO CYCLE sequence has handed out its last value.

Can this report fix a full identity column? No, and nothing should. Changing an int to a bigint on a large table is a migration. The report gives you a checklist.

Do negative increments work correctly? Yes. They count toward the type’s minimum, and the percentage reflects that.

Where did Identity Column Usage go? It is this report, renamed Key Exhaustion when it gained the Sequences view. It is in the same place in the tree.

---