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.
- Remove tblCity table from publication.
- Change the datatype from int to bigint and add this table back to publication.
- 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.
