Identity Column Usage
Overview
The Identity Column Usage report answers one question with a hard deadline attached: how much runway does each identity column have left before it hits the top of its type and inserts start failing with error 8115.
Every column 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.typesonsystem_type_id, which fans out. A user-defined alias type overintmatched both the alias row and the base row, and duplicated the table in the report. decimalandnumericidentity 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.
Where to find it
A database-level report. Select a database in the tree, then open Identity Column Usage.
The page title reads Identity Column Usage for <database name>.
The verdicts
| Verdict | Range used |
|---|---|
| Critical | 90% or more |
| Watch | 70% or more |
| (none) | Below 70% |
Verdict chips appear at those two thresholds, and the headline names the worst offender in plain words rather than making you find it.
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.
Reading the chart
The chart draws the busiest 15 columns; the grid carries everything.
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, decimal, numeric. |
| Last Value | The current identity value. |
| Max Value | The top of the type’s range – or the bottom, for a negative increment. |
| Remaining | The headroom the arithmetic implies. |
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.
The toolbar
| Button | What it does |
|---|---|
All · Watch · Critical |
Narrows chart and grid together, without re-querying. |
Refresh |
Reload now. |
The chart draws the busiest 15 columns; the grid carries everything.
Right-click actions
Nothing on this page runs anything against the database. Fixing a nearly-full identity column is a migration, not a click.
| Item | What it does |
|---|---|
| Copy identity headroom check script | A query you can schedule to watch the column yourself. |
| Copy bigint migration checklist script | Offered on the columns that need it – the steps, in order, with the parts that need an outage marked. |
| Copy column name | Just the name. |
How to read the report
- Filter to Critical. If anything is there, it has a deadline.
- Read Remaining, not just Percent. Ten percent of an
intis still two hundred million values; ten percent of asmallintis three thousand. - Compare Remaining against your insert rate. That gives you a date, which is the only form of this number anybody can plan around.
- Check the Watch rows too. Seventy percent is when a migration is comfortable rather than urgent.
- Copy the migration checklist for anything critical and read it before scheduling.
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.
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. Nothing is stored.
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.
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.