Suppose you do a simple single row insert on a replicated table at the subscriber and find it fails with the message below:
The insert failed. It conflicted with an identity range check constraint in database ‘TestSub’, replicated table ‘dbo.tCity’, column ‘ID’.
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.
The table is merge-replicated and we are using automatic range management of the identity column. OK – this might be normal behaviour. But what if we know we haven’t filled the whole range????
Running the code below returns the top city added.
select top 1 * from [dbo].[tCity]
order by id desc
While the merge check constraint on the table is:
CHECK NOT FOR REPLICATION (([ID]>(24501) AND [ID]<=(25001) OR [ID]>(25001) AND [ID]<=(25501)))
So – 24839 should be the next ID, is fine according to the check constraint and yet it is failing! Why?
According to select IDENT_CURRENT( ‘tCity’ ) the current value is 25504 so that is consistent with the check constraint complaining.
Well – this could be related to an insert that failed as explained in a previous article here. If this is not relevant, you should check the SQL Error log – if the SQL Service was unexpectedly restarted, the identity cache will have been flushed. The identity cache is a set of values held in memory for optimisation purposes. If SQL restarts unexpectedly all the unused identity values in the cache are lost, and SQL errs on the side of caution and resets the current identity value to be the maximum of the values it lost. You can find out more about this in the details of trace flag 272 here . We can configure it at the instance level using this trace flag or at the database level for SQL 2017 using scoped configurations.
If you want to play around with a repro there are a few around but I found this one easy – just implement replication on top of the setup.
In the case above we have 2 options – we can run the merge agent which will assign a new range, or we can reset the current identity value manually using DBCC CHECKIDENT. Remember we are talking about the subscriber here. If this is the publisher and the current identity value is the max of the current range we are lucky because the merge trigger will assign a new range for us directly. Otherwise we have to again run the merge agent, reset manually, or run sp_adjustpublisheridentityrange.
Thanks to Dave Wilce for pointing this one out.