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

Issue:

Assume that you have tables with Identity columns declared as datatype INT and you are using Auto Identity management for those articles in a Merge Publication. This Publication has one or more subscribers and you tried to re-initialize one subscriber using a new Snapshot. The Merge agent fails with this error:

Source:  Merge Replication Provider

Number:  -2147199417

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

Cause:

The Identity range this Merge agent is trying to allocate exceeds the maximum value an INT datatype can have.

Resolution

Assume that publisher database has only one Merge publication with 2 subscribers, and your merge articles have this definition:

exec sp_addmergearticle @publication = N'MergeRepl_ReproDB', @article = N'tblCity', @source_owner = N'dbo', @source_object = N'tblCity', @type = N'table', @description = N'', @creation_script = N'', @pre_creation_cmd = N'drop', @schema_option = 0x000000004C034FD1, @identityrangemanagementoption = N'auto', @pub_identity_range = 1000, @identity_range = 1000, @threshold = 90, @destination_owner = N'dbo', @force_reinit_subscription = 1, @column_tracking = N'false', @subset_filterclause = N'', @vertical_partition = N'false', @verify_resolver_signature = 1, @allow_interactive_resolver = N'false', @fast_multicol_updateproc = N'true', @check_permissions = 0, @subscriber_upload_options = 0, _tracking = N'true', @compensate_for_errors = N'false', @stream_blob_columns = N'false', @partition_options = 0

exec sp_addmergearticle @publication = N'MergeRepl_ReproDB', @article = N'tblCity1', @source_owner = N'dbo', @source_object = N'tblCity1', @type = N'table', @description = N'', @creation_script = N'', @pre_creation_cmd = N'drop', @schema_option = 0x000000004C034FD1, @identityrangemanagementoption = N'auto', @pub_identity_range = 1000, @identity_range = 1000, @threshold = 90, @destination_owner = N'dbo', @force_reinit_subscription = 1, @column_tracking = N'false', @subset_filterclause = N'', @vertical_partition = N'false', @verify_resolver_signature = 1, @allow_interactive_resolver = N'false', @fast_multicol_updateproc = N'true', @check_permissions = 0, @subscriber_upload_options = 0, _tracking = N'true', @compensate_for_errors = N'false', @stream_blob_columns = N'false', @partition_options = 0

You can run this query against the Published database to see what articles range is full or have very few values left:

select a.name,
       max_used=max_used,
       diff_pub_range_end_max_used=range_end - max_used, --this tells how many values are left
       pub_range_begin=range_begin,
       pub_range_end=range_end
from dbo. MSmerge_identity_range b ,
       sysmergearticles a
where
       a.artid = b.artid
       and is_pub_range=1
order by max_used desc

The result:

name           max_used               diff_pub_range_end_max_used      pub_range_begin           pub_range_end
-------------- ---------------------- -------------------------------- ------------------------- -------------
tblCity        2147483647             0                                2147477647                2147483647
tblCity1       6001                   2147477646                       1                         2147483647

As you see from above diff_pub_range_end_max_used column is zero for tblCity.

When Merge agent runs depending on how many servers are involved it has to allocate 2 ranges for each. In the example above we have Publisher and 2 subscribers and @identity_range is 1000. So, we will have to allocate range for 3 servers i.e., 3 * (2*1000) = 6000

Our diff_pub_range_end_max_used should be greater than 6000, only then we will be able to allocate a new range for all the servers.

Resolution

To resolve the issue do the following:

1. Remove tblCity table from publication.
2. Change the datatype from int to bigint and add this table back to publication.
3. Then generate a new snapshot. It will generate snapshots for all articles, but only this 1 table will be added back to the existing Subscribers.