Suppose you do a simple single row insert on a replicated table 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 it 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.
In this case 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.
Thanks to Dave Wilce for pointing this one out.