SQL Server DBCC CHECKIDENT: Check and Reseed Identity

A logging table grew to forty million rows, the bulk of it was archived out, and now only the last week's data remains — but the next insert still gets identity value 40,000,001, leaving a vast gap below it. Or a botched data load left the seed sitting far above the real maximum, and future inserts skip millions of values. DBCC CHECKIDENT closes both gaps in a single statement, and knowing how to check before you reseed keeps it from creating a worse problem than it solves.

Purpose and Overview

After archiving the bulk of a table's rows, the next inserted identity value still picks up where the deleted rows left off — leaving a multi-million-row gap that DBCC CHECKIDENT can close in one statement. The command reads or sets the current identity value, the internal counter SQL Server increments to produce the next IDENTITY value. A plain DELETE never touches that counter, which is why deleting the high rows of a table leaves the seed stranded high above what remains.

CHECKIDENT has two modes that matter. With NORESEED it is read-only: it reports the current identity value alongside the maximum value actually present in the column, so you can see at a glance whether they have drifted apart. With RESEED it writes: it sets the current identity value either to a number you specify or, in its automatic form, back down to the column's true maximum. The discipline that prevents accidents is simple — always check with NORESEED first, decide, then reseed.

This is the single-table operational workflow: you have one table with a known gap or a mis-set seed, and you want to correct it deliberately. (For sweeping every identity column on a database at once, see the bulk-audit companion linked at the end.) The risk to respect is reseeding below the existing maximum value, which sets up duplicate-key collisions the moment inserts catch back up.

Code Breakdown

The script checks first, then shows the three ways to reseed, plus a catalog query to inspect the column's identity metadata.

 1-- (a) READ-ONLY: report current identity value vs. the real max in the column.
 2DBCC CHECKIDENT ('dbo.AuditLog', NORESEED);
 3
 4-- (b) SET an explicit value: the next insert will be @value + increment.
 5DBCC CHECKIDENT ('dbo.AuditLog', RESEED, 1000);
 6
 7-- (c) AUTO-CORRECT: reseed to the current maximum value in the column.
 8--     The next insert continues cleanly from there.
 9DBCC CHECKIDENT ('dbo.AuditLog', RESEED);
10
11-- (d) Inspect the identity metadata directly.
12SELECT
13    OBJECT_NAME(ic.object_id)        AS table_name,
14    ic.name                          AS column_name,
15    ic.seed_value,
16    ic.increment_value,
17    ic.last_value,
18    IDENT_CURRENT('dbo.AuditLog')    AS ident_current
19FROM sys.identity_columns AS ic
20WHERE ic.object_id = OBJECT_ID('dbo.AuditLog');

NORESEED: the check

Form (a) returns two numbers: the current identity value and the column's actual maximum. If they match, the seed is healthy. If the current value is far higher than the maximum — the classic post-archive gap — you have a candidate for reseeding. Run this first, every time; it changes nothing and tells you whether action is even warranted.

RESEED with an explicit value

Form (b) sets the current identity value to exactly what you pass. The next inserted row will be that value plus the column's increment (so RESEED, 1000 on a step-1 column yields 1001 next). Use the explicit form when you want the sequence to continue from a deliberate point — for example, leaving headroom above an external system's range.

RESEED auto-correct

Form (c), with no value, resets the current identity to the maximum value currently in the column. This is the right tool for closing a gap after a delete or archive: inserts resume immediately above the surviving rows with no wasted range and no collision risk.

The empty-table special case

There is a gotcha worth memorizing: reseeding an empty table behaves differently. After RESEED on a table with no rows, the first inserted row receives the seed value itself, not seed-plus-increment. The same is true right after TRUNCATE TABLE, which automatically resets the seed. Account for this when you expect the next value to be one increment higher.

The danger: reseeding below the maximum

If you RESEED to a value below rows that already exist, the counter will climb back through values already in use, and every insert that lands on an existing key throws a primary-key or unique-constraint violation. Always confirm with NORESEED that your target value clears the existing maximum before setting it.

Watching for int exhaustion

The other reason to run NORESEED periodically is capacity. An int identity tops out at 2,147,483,647, and a high-churn table that recycles values through deletes can burn through that range faster than its row count suggests — the counter only ever climbs. When a NORESEED check shows the current value approaching the ceiling, that is the signal to plan a bigint migration before inserts start failing with arithmetic-overflow errors. A quick sweep of sys.identity_columns joined to sys.columns lets you find every int identity and compare its last_value against the type's maximum in one query, turning an eventual outage into a scheduled change.

Key Benefits and Use Cases

• Close post-archive gaps — reseed to the surviving maximum so identity values resume contiguously after a large delete.
• Fix a mis-set seed — correct a counter left too high (or too low) by a bad bulk load or migration.
• Reset staging tables — return a reusable load table's identity to a known starting point between runs.
• Reserve value ranges — set an explicit seed to leave room for an external system that owns part of the range.
• Audit before acting — NORESEED gives a non-destructive read of seed-versus-max drift.
• One-statement correction — no table rebuild, no data movement, just a metadata change.

Performance Considerations

• Lightweight metadata operation: CHECKIDENT changes a counter, not data, so it is effectively instant regardless of table size.
• Reseeding below max risks duplicate keys: the most serious hazard — collisions appear later, as inserts catch up, not immediately.
• Replication and identity ranges: in merge replication, identity ranges are managed automatically; manual reseeds can collide with the range manager.
• Schema-qualify the name: always pass schema.table; an unqualified name resolves against the default schema and may hit the wrong object.
• IDENTITY_INSERT interplay: rows inserted while IDENTITY_INSERT was ON can push the real max above the counter, which NORESEED will reveal.

Practical Tips

• Always run NORESEED first — never reseed blind; read the current-vs-max gap and decide deliberately.
• Snapshot the value before changing it — record the old current identity so you can roll back a mistaken reseed.
• Remember TRUNCATE resets the seed — after TRUNCATE TABLE the counter is already back to the seed; an extra RESEED is redundant.
• Watch for identity exhaustion — an int identity caps near 2.1 billion; a NORESEED check nearing that ceiling is a signal to plan a bigint migration.
• Mind the empty-table rule — the first insert after reseeding an empty table is the seed value itself, not seed-plus-increment.

Conclusion

DBCC CHECKIDENT is the precise, one-statement way to inspect and correct an identity column's counter — invaluable after archiving rows or recovering from a bad load. Check with NORESEED, confirm your target clears the existing maximum, then reseed. For sweeping every identity column at once, see the bulk identity-column analysis companion, and for the full toolkit, the complete DBCC command reference.

References

• DBCC CHECKIDENT (Transact-SQL) — the official reference for NORESEED and RESEED behavior, including the empty-table case.
• sys.identity_columns (Transact-SQL) — the catalog view exposing seed, increment, and last value.
• sqlserver-kit — community scripts for identity management and table maintenance.