The insert failed. It conflicted with an identity range check constraint
Taiyeb Zakir
Microsoft SQL Server Escalation Support Services
When replicating identity columns https://docs.microsoft.com/en-us/sql/relational-databases/replication/publish/replicate-identity-columns and using Auto Identity management, you need to make sure ranges are defined properly. The default “ranges” for automatic identity range management, while good 20 years ago, may need to be tuned for today’s higher workloads.
When the range is full on the Publisher and the user doing the insert is a db_owner, a new range is allocated automatically on the Publisher by the Replication created trigger on the user table.
When the range is full on the Subscriber, the next time the Merge or Distribution agent executes, it will allocate a new range.
Had a case recently where inserts from the Application on the Publisher was failing with this error and they were using Merge Replication:
Msg 548, Level 16, State 2, Line 61
The insert failed. It conflicted with an identity range check constraint in database ‘ReplMerge_PUB’, replicated table ‘dbo.ReplTest3’, column ‘ID3’. 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.
The statement has been terminated.
Found an Insert using Select inserting more rows in 1 transaction than Identify range allowed. With Merge, @identity_range parameter controls the identity range size initially allocated both to the Publisher and to Subscribers with client subscriptions.
Customer had set @identity_range to 10K and threshold was set to 80. We increased @identity_range to 100K. This increased the threshold range and prevented errors with the Insert.
You can also check how often ranges are being filled and reset by querying the table MSmerge_identity_range_allocations in the distribution database. Range allocations should be large enough that range resets are rare, not common event. You can change the range in SQL Management Studio under article properties or via sp_changemergearticle stored procedure.
See also:
https://blogs.msdn.microsoft.com/repltalk/2010/03/03/all-about-identity-range-management/
https://docs.microsoft.com/en-us/sql/relational-databases/replication/publish/replicate-identity-columns
https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-changemergearticle-transact-sq
