SQL Server Performance Forum – Threads Archive

identity ranges problem

Hello I have the following problem: – I have a transactional replication between a publisher and a few suscribers,
– The servers are SQL 2000.
– I have Identities columns in some of the tables
– I’m using the Automatic Identity Range Handling The problems happens when the publisher or one of the suscriber goes down, (it happens relativly often and we can’t do anything against it), so when the connection is restablished, the merge agent assign a new identity range to the publisher or
the suscriber(the one that went down). Everytime a server goes down, it "eats" a idetity range, doesnt matter how many idenities have been used, and i am running out of identity ranges. I do want to keep using the Automatic Identity Range Handling to manage the replication activity because
changing it to manual would be really hard for us. Im new at this but I see two ways to solution it:
1) avoid that the distribution agent assign a new identity range when a server goes down
2) let the distribution agent assign a new identity range, and then reestablish the identity to the heigest id value+1 (using CHECKIDENTITY()), but i think i would have to do some extra things to make the publisher to be sincronized with the suscribers (maybe modify a table on the publisher or something) Could someone please tell me what is the easier way to solution it, if there is another easier way and how to implement it, or the other two? thanks for your attention

---

I believe you have merge replication not transactional replication as you mentioned… Why server down tied with identity values?
Only identity values will exhaust/finish when there is a failed insert… What are the values for the following…
Range size at Publisher:
Range size at Subscriber:
Assign a new range %: Check the MSrepl_identity_range table.. and constraints on the table at subscriber as well as publisher..
MohammedU.
Moderator
SQL-Server-Performance.com

---

]]>