Identity columns are commonly used across numerous database applications, providing the database owner a low overhead, logical approach to stamp each row with a unique identifier.
This approach is challenged when used in a replication environment, where publishers, distributors and subscribers all contribute to the current ‘seed’. Depending on whether you’ve employed merge or transactional replication, the identity range will be handled by the publisher or the distributor respectively.
In theory, yes, in practice, occasionally you must return to resolve identity range problems (the identity range is a method the replication engine uses to manage the distribution of identities).
In a merge environment your issue can be the seed, with a subscriber falling behind, in merge or transactional, the range can be the issue.
If the range set (based on the data type and increment) is exhausted, or a user other than the database owner inserts or the data type has been exhausted, or you’re breaching the minimum part of the check constraint, you may bump into an identity range and the server will begin reporting “The insert failed. It conflicted with an identity range check constraint in….If the identity column is automatically managed by replication, update the range as follows: for the Publisher, execute sp_adjustpublisheridentityrange; for the Subscriber, run the Distribution Agent or the Merge Agent.”
Showing some resolve
If you’re working with a merge replication you should reseed:
- stop your subscribers (so the seed won’t move),
- grab the maximum identity from the publisher and all the subscribers – IDENT_CURRENT(‘table_name’),
- reseed the publisher – DBCC CHECKIDENT(‘table_name’,’reseed’,@maxid).
Whether your replication environment is merge or transactional, the publisher range needs to be adjusted, so in most circumstances a simple execution of the following line is the solution you need:
exec sp_adjustpublisheridentityrange @table_name=’table_name’
This command adjusts the table’s check constraint again based on current seed. increment and data type, thus, in some instances the adjustment hasn’t resolved the issue. If you’re still impeded by the identity range, visit the table’s check constraints, look for the range constraint (repl_identity_range…) and manually modify.
SQL server’s replication engine is a powerful tool, identity range management is a good example of the impressive accuracy and self protection the solution implements.