Press "Enter" to skip to content

Merge Replication: Publisher Failed to Allocate a New Set of Identity Ranges

Taiyeb Zakir brings back bad memories for me:

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

Click through for one solution. I’ve seen the error pop up in other cases where the column was already a bigint or the ranges were nowhere near the max and have had to mess around with range values to get things working again. My simple advice with merge replication and identity integers is to include something in addition to the identity column that guarantees uniqueness–for example, ID plus a column for subscriber name, if that makes sense in your situation–and don’t auto-assign ranges. Yeah, you may end up with five rows with ID = 1, but it doesn’t matter because there’s something making that row unique.