SQL Server DBCC CHECKIDENT: Check and Reseed Identity

calendar Jun 4, 2026 / · 7 min read · sql server identity columns database administration maintenance scripts sql scripts dbcc commands table maintenance identity reseed DBCC CHECKIDENT sys.columns sys.identity_columns  ·
Share on: linkedin copy

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 can correct both, but only if you use the right form: the plain RESEED that most scripts reach for raises a counter that is too low and never lowers one that is too high.

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-value gap. 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 to a number you specify, or, in its automatic form with no value, raises the counter to the column's maximum when the counter has fallen below it. The automatic form does not lower a counter that sits above the maximum, which is the post-archive case. Check with NORESEED first, decide which case you have, 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 a counter that is BELOW the column maximum.
 8--     Raises it to the maximum; it never lowers a counter that is above it.
 9DBCC CHECKIDENT ('dbo.AuditLog', RESEED);
10
11-- (c2) LOWER a stranded counter after an archive: set it low, then let
12--      the automatic form raise it back to the surviving maximum.
13DBCC CHECKIDENT ('dbo.AuditLog', RESEED, 0);
14DBCC CHECKIDENT ('dbo.AuditLog', RESEED);
15
16-- (d) Inspect the identity metadata directly.
17SELECT
18    OBJECT_NAME(ic.object_id)        AS table_name,
19    ic.name                          AS column_name,
20    ic.seed_value,
21    ic.increment_value,
22    ic.last_value,
23    IDENT_CURRENT('dbo.AuditLog')    AS ident_current
24FROM sys.identity_columns AS ic
25WHERE 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, corrects a counter that has fallen below the column's maximum by raising it to that maximum. It does nothing about a counter that sits above the maximum. For the post-archive gap, Microsoft documents two methods: read the maximum with NORESEED and pass it to RESEED, <value> (form b), or reseed to a low value and then run the automatic form, which raises the counter back to the surviving maximum (form c2). Either way, inserts resume immediately above the rows that remain.

The empty-table special case

The next value after RESEED, <value> depends on how the table got empty. If no rows have been inserted since the table was created, or every row was removed with TRUNCATE TABLE, the first row inserted uses the reseed value itself. If rows are present, or every row was removed with DELETE, the next row uses the reseed value plus the increment. So an archive table emptied by DELETE and reseeded to 0 gives its next row the value 1, while the same table emptied by TRUNCATE and reseeded to 0 gives it 0.

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 (explicitly, or low-then-automatic) 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, and transactional replication with updates at the Subscriber, replication assigns identity ranges itself; check how ranges are managed on that publication before reseeding a published table.
  • 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: an explicit value inserted with IDENTITY_INSERT ON that is larger than the current identity value becomes the new current value automatically (positive increment), so explicit inserts do not leave the counter behind the maximum.

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 how the table was emptied — after TRUNCATE (or on a never-used table) the first insert takes the reseed value itself; after DELETE it takes reseed value plus increment.

Conclusion

DBCC CHECKIDENT inspects and corrects an identity column's counter without touching the data, which makes it the standard fix after archiving rows or recovering from a bad load. Check with NORESEED, pick the form that matches your case, and confirm the new value clears the existing maximum. 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

Posts in this series