Results 1 to 8 of 8
  1. #1
    Join Date
    Feb 2007
    Posts
    8

    Unanswered: Running out of Identity Ranges

    Hello

    I have the following problem:

    - I have a transactional replication between a publisher and a few suscribers,
    - The servers are SQL 2003.
    - 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 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 marge agent assign a new identity range when a server goes down
    2) let the merge agent assign a new identity range, and the reestablish the identity to the heigest id value (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

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    you're running out of 4 billion id's?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    Jan 2004
    Location
    In a large office with bad lighting
    Posts
    1,040
    1. Get rid of the identity column.
    2. see number 1.
    3. http://support.microsoft.com/kb/322910/en-us
    4. sp_adjustpublisheridentityrange

    -- This is all just a Figment of my Imagination --

  4. #4
    Join Date
    Feb 2007
    Posts
    8
    mmmhhhh, 2 billions, because i use just positive numbers, hehehe
    but yes, you are right. I'll change the range sizes.

    i'm looking out for a way to modify the value that the publisher will give to the next suscriber that need a range, would it be possible?

    i dont want to drop any table and rebuild them on my db

    thanxs for you help

  5. #5
    Join Date
    Feb 2007
    Posts
    8
    Quote Originally Posted by tomh53
    1. Get rid of the identity column.
    2. see number 1.
    3. http://support.microsoft.com/kb/322910/en-us
    4. sp_adjustpublisheridentityrange
    thanxs for you answer

    I cant drop that column because the values that contains are usefull for me outside de system

    I wonder if there is a way to manually modify the value that the publisher will give to the next server that will need a range?

  6. #6
    Join Date
    Jan 2004
    Location
    In a large office with bad lighting
    Posts
    1,040
    Nope ... you can't have it both ways. Either you manually manage the identity value, or use sp_adjustpublisheridentityrange if you are letting sql server auto-manage the value.

    I prefer to use the manual method. I set the column seeds 500000 apart, and agressively purge the stale data ... thereby never letting the values collide. I don't care if they go negative ... i just don't want them to overlap.

    -- This is all just a Figment of my Imagination --

  7. #7
    Join Date
    Jan 2004
    Location
    In a large office with bad lighting
    Posts
    1,040
    I do not like identity columns nor green eggs and ham, I do not like them Sam I am
    (with apologies to Theodor Seuss Geisel)

    -- This is all just a Figment of my Imagination --

  8. #8
    Join Date
    Feb 2007
    Posts
    8
    Quote Originally Posted by tomh53
    I do not like identity columns nor green eggs and ham, I do not like them Sam I am
    (with apologies to Theodor Seuss Geisel)
    hehe, thanxs for you answer

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •