I have an SQL Server 2014 merge-replication topology with a publisher and 5-6 subscribers on several 2012R2 servers. There are several tables involved in the replication (50-60), with identity columns to many of them.

I have recently dropped the replication and recreated it from scratch, because among other things, there were rather frequent messages (maybe 1-2 times per day) of this type:

"The Publisher failed to allocate a new set of identity ranges for the subscription...".

Leaving the server alone, it would renew the identities after a while, but it seemed there was something wrong. After the recreation of the replication, I again started receiving such messages (perhaps less frequently now). I know that the network connections between the servers are fast (all but one) but overloaded.

The identity columns on all tables are all of type INT and the maximum number reached in a table is less than 10 millions, which is much less than about 2 billions INT can handle). All subscriptions are "push" (continuous, with the default interval of 60 seconds) and every task is being executed on the publisher. I have increased the "subscriber range size" to 2500 from 1000 and decreased the "range threshold percentage" to 20 from 80, for some articles which have the greatest number of new rows per day, but this didn't seem to help much.

What may be causing such problems and what can I do to make them occur less frequently (if not eliminate them)?

Additional information: All tables taking part in the replication, have "NOT FOR REPLICATION" and "NOT NULL" in the IDENTITY column, in their associated CREATE TABLE statement. The SQL Server build is 12.0.4100.1.

  • Are identity columns set to not for replication ? – Kin Sep 24 at 12:05
  • Yes, i checked all tables taking part in the replication, they all have "NOT FOR REPLICATION NOT NULL" in the IDENTITY field, in their associated CREATE TABLE statement. Updated the main post to reflect this info. – FaultyOverflow Sep 24 at 16:08
  • Thats interesting .. as NOT FOR REPLICATION should be YES .. why is it NULL ? Can you run SELECT name, range_begin, range_end, max_used FROM MSmerge_identity_range mir INNER JOIN sysmergearticles sma ON mir.artid = sma.artid WHERE is_pub_range = 1 AND range_end <= max_used + pub_range Also, what exact build you are running for sql server 2014 ? – Kin Sep 24 at 16:31
  • You misunderstood me. The field is marked as both NOT FOR REPLICATION and also NOT NULL. When i create the CREATE TABLE script, i see it like that. As for the build, it's 12.0.4100.1. – FaultyOverflow Sep 24 at 20:14

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Browse other questions tagged or ask your own question.