Identity Range Allocations in Merge Replication
-
Hi All,
I replicate using merge replication, 3 databases from our production server (which acts as the publisher and distributor) to two subscribers. This set up has been running sucessfully for about 18 months.
I have recently experienced an issue where the identity range allocated to my subcribers has jumped massively. This seems to have happened after a re-initialisation, where I expected the old ranges to be dropped and replaced, but based on the content of dbo.MSmerge_identity_range_allocations, this happened many, many times. Interestingly, the time stamp on these requests coincides with the time when my merge agent would run...
The example of my supplier table pictured, which we expect a Supplier ID range somewhere between 10000 and 12000, now is assigning a range of 5556411!
Has anyone else seen this happen before, and what would be the steps to fix this issue? Is it possible to reset the identity range to use some of the numbers it's 'jumped' over?
Cheers,
Gary Bradley
- gary.bradley - Monday, October 23, 2017 6:54 AMHi All,
I replicate using merge replication, 3 databases from our production server (which acts as the publisher and distributor) to two subscribers. This set up has been running sucessfully for about 18 months.
I have recently experienced an issue where the identity range allocated to my subcribers has jumped massively. This seems to have happened after a re-initialisation, where I expected the old ranges to be dropped and replaced, but based on the content of dbo.MSmerge_identity_range_allocations, this happened many, many times. Interestingly, the time stamp on these requests coincides with the time when my merge agent would run...
The example of my supplier table pictured, which we expect a Supplier ID range somewhere between 10000 and 12000, now is assigning a range of 5556411!
Has anyone else seen this happen before, and what would be the steps to fix this issue? Is it possible to reset the identity range to use some of the numbers it's 'jumped' over?
Cheers,
Gary Bradley
When you reinitialize, new ranges are assigned to each subscriber. It doesn't drop the old values and resue or use up those which would be in the range just prior to reinitializing. It just assigns new, incremented ranges. Rollbacks will also impact the identity values.
The identity values also increment by 1000 on restarts and failovers beginning with SQL Server 2012 - a lot of people reported that as a bug but MS said it was by design. It did get kind of addressed in SQL Server 2017 with a database scoped configuration of IDENTITY_CACHE. But that does you no good on 2014.
There isn't a way to reuse the values that were jumped over.Sue
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply