3

I have Merge Replication set up with a SQL 2005 Publisher/Distributor and roughly 100 SQL 2005 Express Subscribers. Everything was working fine for months and now all of a sudden everyone is getting the below errors.

I have been Googling around but to no avail. Can anyone offer some insight? I even tried deleting a user's Subscription. I also tried running -->

sp_adjustpublisheridentityrange @publication='MyDB'

Anyway, here are the errors -->

Error messages:
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
Not enough range available to allocate a new range for a subscriber. (Source: MSSQLServer, Error number: 20668)
Get help: http://help/20668
Failed to allocate new identity range. (Source: MSSQLServer, Error number: 21197)
Get help: http://help/21197
Refracted Paladin
  • 345
  • 7
  • 19

1 Answers1

2

First, are your machines patched with at least Service Pack 3? This error was often thrown by a bug fixed by Service Pack 2 Cumulative Update 4. That may be a place to start.

If you are all patched up, I'd next check the data type of your identity columns. If they are currently INTs, for example, and the publisher is trying to allocate a range that exceeds the maximum INT value (2,147,483,647), you would get that error. You could resolve it by changing your identity field to a BIGINT. With 100 subscribers, your publisher has had to allocate a large number of ranges, so this could be likely.

Hope this helps.

Brian Knight
  • 1,165
  • 2
  • 8
  • 17
  • Are there any known issues with upgrading systems currently in a Replication Topology to Service Pack 3 from 2? Thanks. – Refracted Paladin Feb 22 '10 at 17:12
  • I've not seen any. I've upgraded systems using all three types of replication from SP2 to SP3 and have had no issues at all. Of course, I always recommend testing in a QA environment first ;). Here's the link to the KB article for that fix: http://support.microsoft.com/default.aspx/kb/941989 – Brian Knight Feb 22 '10 at 17:30