Sql-server – Replication sync failing; Publisher out of Identity ranges

merge-replicationreplicationsql serversql-server-2005

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

Best Answer

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.

Related Topic