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

This article is contributed. See the original author and article here.

Problem:

===========

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.

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:

============

Identity range Merge agent is trying to allocate, exceeds 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, @delete_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, @delete_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

 

 

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.

 

To resolve the issue.

 

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.

Brought to you by Dr. Ware, Microsoft Office 365 Silver Partner, Charleston SC.