Merge Replication Identity Range issue

Question

Hello everyone.

I had an issue with replication that has been setup with no issues for a few months now.  Early last week, I started to recieve the error message:

The Publisher failed to allocate a new set of identity ranges for the subscription. This can occur when a Publisher or a republishing Subscriber has run out of identity ranges to allocate to its own Subscribers or when an identity column data type does not support an additional identity range allocation. If a republishing Subscriber has run out of identity ranges, synchronize the republishing Subscriber to obtain more identity ranges before restarting the synchronization. If a Publisher runs out of identit (Source: MSSQL_REPL, Error number: MSSQL_REPL-2147199417)
Get help: http://help/MSSQL_REPL-2147199417

When I saw this, I ran 'sp_adjustpublisheridentityrange' to update the range and everything started working again.

So, I have a couple questions:

1. Why didn't the replication process handle updating the identity range constraint on the publisher automatically?  I was told by the developer that the application inserts records individually, not in batches.  The articles were setup with 1000 as the range and 80 as the threshold.

2. Before 'sp_adjustpublisheridentityrange' was ran, the range was around 124000->125000, but now the constraint has...  ([Row]>(12505001) AND [Row]<=(12506001) OR [Row]>(12506001) AND [Row]<=(12507001)).  Why is the new range so much higher than the previous range?

I am just mainly confused as to why it didn't update the range automatically.

Any help would be greatly appreciated.

Thanks.

Jarret

Answer 1

A little more information on #2 from above...

I looked in the 'MSmerge_subscriptions' table on the distribution database to get the subid GUID.  For simplicity, let's say it's 58DB.  I forgot to mention before, there is only one subscription setup for this publication.

Then, I checked the 'MSmerge_identity_range' in the publication database...  There are 4 records for the table article in question, but there are 2 different subid's for the 2 publisher and 2 subscriber range records.  Here's what I see, maybe this will clarify what I'm trying to explain.  I am going to use 4 characters for the GUID's.


subid: 58DB
artid: F736
range_begin: 126001
range_end: 127001
next_range_begin: 127001
next_range_end: 128001
is_pub_range: 0
max_used: NULL

subid: 58DB
artid: F736
range_begin: 128001
range_end: 138001
next_range_begin: 138001
next_range_end: 148001
is_pub_range: 1
max_used: NULL

subid: EB96
artid: F736
range_begin: 12505001
range_end: 12506001
next_range_begin: 12506001
next_range_end: 12507001
is_pub_range: 0
max_used: NULL

subid: EB96
artid: F736
range_begin: 124001
range_end: 2147483647
next_range_begin: NULL
next_range_end: NULL
is_pub_range: 1
max_used: 12507001


When I check the range constraint on the subscriber, it is:  ([Row]>(126001) AND [Row]<=(127001) OR [Row]>(127001) AND [Row]<=(128001)).  Which is what I suspect it should be, based off of the records above.

The range constraint on the publisher is:  ([Row]>(12505001) AND [Row]<=(12506001) OR [Row]>(12506001) AND [Row]<=(12507001)).

I'm not sure where the records for subid EB96 are coming from, could someone shed some light on all of this?

Thanks.

Jarret

Answer 2

I am not sure either.

A couple of points

1) Frequently the range is not adjusted with the first run, but will be on the second. I have observed this behavior many times and what I do now is just sync again and that normally clears it.
2) The range assigned depends on the ranges already assigned. So if you have more than subscriber you can expect to see a jump like this. How many subscribers do you have?

---

looking for a book on SQL Server replication? http://www.nwsu.com/0974973602.html looking for a book on SQL Server 2008 Administration? http://www.amazon.com/Microsoft-Server-2008-Management-Administration/dp/067233044X looking for a book on SQL Server 2008 Full-Text Search? http://www.amazon.com/Pro-Full-Text-Search-Server-2008/dp/1430215941

Answer 3

Hello Hilary, thanks for the reply.

1. What do you mean by 'the first run'?
2. I only have one subscriber.  This was setup over 3 months ago, and I haven't had any issues with it at all this whole time.  I don't think I have needed to reinitialize since it was first setup either.  It's bizarre (to me).

Have you ever seen the issue I described in my followup post on this thread?  Where there are additional records in the 'MSmerge_identity_range' table that don't match the subscription id from the 'MSmerge_subscriptions' table?

Jarret

Answer 4

Hi Jarret,
  Were you able to resolve this problem. I am having the same problem as you have described.
I too have 1 subscriber. I am having to run the 'sp_adjustpublisheridentityrange'  almost everyday.
Though the Replicated table has only about 1000-2000 inserts everyday.

Pooja