1

We have an issue with merge replication where we have a background process inserting rows in bulk. These inserts sometimes fail with the message that

The insert failed. It conflicted with an identity range check constraint in database ‘′, replicated table ‘’, column ‘′. 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.

I've done a lot of reading and tinkering with inserts of different sizes and various settings for the Publisher Range Size, Subscriber Range Size, and Range Threshold Percentage. If I set the bulk insert size to something not divisible by 10 and repeatedly insert rows, I cannot find settings that are immune to the insert error. Even if I set Range Threshold Percentage to 50 or 1 it still doesn't allow me to insert batches that are close to 1/3rd the size of the Subscriber Range Size. Should there not be some setting like 1000 rows for the subscriber range and 50% threshold that if I insert 317 rows(prime number) repeatedly it doesn't error? If I were to say, I'm not going to insert more than 5000 rows at a time, does anyone have a recommendation for what to use for the settings that would never result in the insert error?

Zephryl
  • 343
  • 2
  • 9

1 Answers1

0

Without knowing what your existing threshold is and exactly how many rows you are inserting, all i can tell you is that your subscriber range is probably too small.

I also want to ensure you have a clear understanding of what @threshold parameter does. If your range size is 1000, and @threshold = 60, then it will allocate a new range at 600.

So, if you have a max of 5000 rows to be bulk inserted, and your standard range for user/merge insert operations is 1000, with a threshold of 80, you would want your range to be 7500:

(5000 + 1000) / .80 = 7500

This way if you were on the cusp of hitting the end of the user range (say at 999), you could do the 5000 bulk inserts without triggering a new range allocation.

Greg
  • 3,861
  • 3
  • 23
  • 58
  • This is good in theory only. Say I set up your scenario.(Subscriber range of 7500, threshold 80) It works when I insert 5000 rows repeatedly on the publisher, but it's not what you'd expect. Every third insert of 5000 assigns two new ranges of identities. Also, if I try to repeatedly insert 4999 rows, it will fail on the 4th insertion of 4999 rows. I can do this repeatedly. I run sp_adjustpublisheridentityrange, then insert 4999, insert 4999, insert 4999, insert 4999(fails), repeat. This behavior is exactly the same with threshold = 80, threshold = 1, threshold = 10. – Zephryl May 20 '15 at 04:49
  • I guess threshold isn't actually doing anything per MS documentation(taken from https://technet.microsoft.com/en-us/library/ms151736%28v=sql.110%29.aspx): This value must be specified, but it is only used by: Subscribers using queued updating subscriptions; and Subscribers to merge publications running SQL Server Compact or previous versions of other SQL Server editions. For more information, see the "Assigning Identity Ranges" section of Replicate Identity Columns. – Zephryl May 29 '15 at 04:25