How to manually reuse the identity range under the merge replication

This post has been republished via RSS; it originally appeared at: SQL Server Support articles.

when you choose the auto manage the identity range on the merge replication, and some subscriptions dropped, that identity range could not reuse.

see this sample:

lduan_dsd_0-1591583759264.jpeg

 

this sample used too large range for auto managed identity range. after some subscriptions dropped and recreated, the INT range used up:

lduan_dsd_1-1591583852364.png

Above highlighted Ranges in Green are currently used by the publisher and the subscriber.

However the rows which are not highlighted above, the respective subscription doesn’t exist and the ranges allocated are not being used (from 847061831  - 2147061831). This range is allocated to the subscriber which was failing to initialize and we see that range was increasing each time the subscriber was initialized and It has reached the max value applicable for int datatype.

 

Now the error reported:

Number:  20668

Message: Not enough range available to allocate a new range for a subscriber.

Number:  21197

Message: Failed to allocate new identity range.

 

What we can do now?

All replication system tables could manually update and on this case , we can resolve this issue by manually change several replication system table.

 

I will demo it step by step:

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

I created the sub on the test databaseà it triggered 2 range allocated: used 50 as range on the sub and 100 as the range on the pub.

check MSmerge_identity_range and MSmerge_identity_range_allocations:

6.jpg7.jpg

I inserted 1-199 into pub table

I insert 602-639 into sub(test db) table

Start the merge agent to sync all data to pub.

Then I drop this test sub. Check two tables again, we could see the range record still exist.

 

Manually delete:

delete from [dbo].[MSmerge_identity_range_allocations] where subscriber_db = 'test' – in distribution db

delete from [dbo].[MSmerge_identity_range] where subid not in ('CFF49551-FCB7-48D6-AFE4-5F2F0B75D396','05D5C200-CF9A-4137-98B3-58A330391A97')  --keep pub and another sub I want to keep

update [dbo].[MSmerge_identity_range] set max_used ='641' where next_range_end is null  --update the watermark to max used identity on the sub.

8.jpg9.jpg

 

On the last udpate command, I used 641 as the watermark, not 601. 

It considers I will add this subscription back on next test and I inserted 40 rows into this subscription after the range allocated. If you won't add this subscription back, you don't need to use the max identity value to update the max_used on the table MSmerge_identity_range.

 

Add the sub test back: it could reuse the range but the range is move to high 40 rows due to 40 insert command.

10.jpg12.jpg

 

Check table constraint:

 

Test sub: ALTER TABLE [dbo].[Table_1]  WITH NOCHECK ADD  CONSTRAINT [repl_identity_range_CB4021FD_1328_4161_A874_2D732F5DD187] CHECK NOT FOR REPLICATION (([id]>(641) AND [id]<=(691) OR [id]>(691) AND [id]<=(741)))

Pub: ALTER TABLE [dbo].[Table_1]  WITH NOCHECK ADD  CONSTRAINT [repl_identity_range_CB4021FD_1328_4161_A874_2D732F5DD187] CHECK NOT FOR REPLICATION (([id]>(101) AND [id]<=(201) OR [id]>(901) AND [id]<=(951)))

 

Insert 300 rows on both pub and sub to verify range working:

11.jpg

Important: We officially don't support customer to manually change replication system objects(table/view/SP), if you want to manually modify any system table , please contact Microsoft support team to confirm this operation is safe.

 

--Ling Duan

 

REMEMBER: these articles are REPUBLISHED. Your best bet to get a reply is to follow the link at the top of the post to the ORIGINAL post! BUT you're more than welcome to start discussions here:

This site uses Akismet to reduce spam. Learn how your comment data is processed.