Understanding Identity Ranges – when do they fill up?

For a table which is merge replicated and has an identity column what happens – how does it all work? Looking at the article properties we can see the range sizes allocated to the publisher and subscriber. Each one will get an allocated range with a different starting point to make sure they don’t overlap. Notice that the publisher range is a lot bigger than the subscriber but we can sort this out before publishing the table.

So – now for a quick question – what happens if I stop the merge agent from synchronizing and insert a single row in a loop at the publisher and do the same at the subscriber. Let’s say I try to insert 50,000 records this way. Remember the declared range sizes above are 10,000 and 1,000.

Do you have an idea in mind? Will it work or fail? If it fails, at what point will it fail?

The answer, which may be surprising:

Publisher will insert all 50,000 records

Subscriber will insert 2000 records then fail with the following message:

Msg 548, Level 16, State 2, Line 5The insert failed. It conflicted with an identity range check constraint in database ‘TestRepSub’, replicated table ‘dbo.tCities’, 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.

This is interesting. First of all we can see that the range on the subscriber is actually twice the size we declared. This is always the case. Secondly, the publisher is able to assign a new range when needed on the fly, unlike the subscriber. For the subscriber we really need to synchronize to get the next range.

One final point is that this scenario involves single updates. You’ll have a big problem if your code does the following sort of logic to insert a lot of rows:

insert into tcities ([City]) select city from tcities

The relevant code to understand how this works/fails is in the merge triggers and check constraints (for those interested in looking a bit deeper). Note that for an insert, the check constraint is validated beforethe data change and after that the insert trigger fires. This is the order of processing and it is important, because a failed insert will still increase the IDENT_CURRENT value of the column. If we were at the boundary of the check constraint, after one failed insert all others will fail because the trigger never gets an opportunity to allocate a new range (the check constraint prevents further inserts because the next identity value is too high for the defined ranges). The only options are to refresh the publisher range by calling sp_adjustpublisheridentityrange or to run the merge agent which will do the same thing for you or to manually reset the current identity value using DBCC CHECKIDENT.

To avoid such issues on the publisher we can rework the logic to reduce the batch size. Make sure you hit the upper limit of the declared identity range and then do a single row insert and you’ll have a new range allocated. In practice, the only way to ensure this is by using single row inserts.