none
(!) The Publisher failed to allocate a new set of identity ranges for the subscription (!) HELP (!!!)

    Question

  • Hello!

     

    The following problem has arisen:

    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 (Source: MSSQL_REPL, Error number: MSSQL_REPL-2147199417)
    Get help:
    http://help/MSSQL_REPL-2147199417

    It happened after merge replication has been disabled and then enabled and recreated on republishing  subscriber in the republication chain:

     

    Server A -> Server B -> Client

     

    Server A (publisher):

    Transactional replication, Publisher

     

    Server B (republishing subscriber):

    Transactional replication, Subscriber, Push subscription

    Merge replication, Publisher

     

    Client:

    Merge replication, Subscriber, Pull subscription (Web synshronization)

     

    I tried to follow the error message "If a republishing Subscriber has run out of identity ranges, synchronize the republishing Subscriber to obtain more identity ranges before restarting the synchronization" without success.

     

    PLEASE HELP! This is urgent.

     

    Alexander.

    Wednesday, October 17, 2007 4:14 PM

Answers

  • there will be a check constraint on the problem table on the publishing subscriber. See what the values are and then compare this with what is returned via DBCC checkindent - for this table.

    These values should lie within the same ranges, if not you need to run the distribution agent on the publisher to the publisher subscriber until it reassigns a new range.
    Wednesday, October 17, 2007 4:44 PM
    Moderator

All replies

  • there will be a check constraint on the problem table on the publishing subscriber. See what the values are and then compare this with what is returned via DBCC checkindent - for this table.

    These values should lie within the same ranges, if not you need to run the distribution agent on the publisher to the publisher subscriber until it reassigns a new range.
    Wednesday, October 17, 2007 4:44 PM
    Moderator
  • Hi Hilary!

     

    The problem was that since a publication on republishing subscriber has been created, Identity ranges for the table were incorrect and set by default (I guess). Parameter 'Publisher range size' = 10000 for the article that caused the case while the actual identity value was more than 20000.

     

    After I've ran the distribution agent on publisher, they were not affected but stayed the same.

     

    What I've done is I manually changed the parameter for the publication article. This helped, but what will happen when this problem appears next time? Why the range is not being initiated with proper value? Why it didn't go automatically?

     

    Thank you.

     

    Alexander.

     

    Thursday, October 18, 2007 6:35 AM
  • We are having the same problem, even though we have  arange size of 10000, some of the tables run out of identities when they reach 1000. The error happens when we try to insert rows in the publisher, so far it has happened more than 5 times. We update the range running the sproc sp_adjustpublisheridentityrange.

    The range is set up to adjust automatically on every table in the publication. This never happened in our SQL Server 2000 instance. On this instance the ranges were adjusted automatically. This started happening when we upgraded our production servers to 2005.

    We are wondering if this is a bug as it obviously doesn't work as it should. We have gotten the answer "this is as it is by design" before, but the fact that we have to manually run a sproc to adjust the ranges seems to be a very poor design.

    Any comment is more than welcome. We will open a ticket if this is not addressed ASAP as this is not an acceptable behavior.
    Thursday, November 29, 2007 6:18 PM
  • For merge replication, is there a way to find out what table is causing the identity problem? One downside about merge replication is that it will sometimes not tell you what table is the culprit. It is true especially on errors such as this.

    MCP, MCSD, MCDBA (ANXA)

    Monday, August 13, 2012 5:34 AM