none
Merge Replication Identity range check constraint RRS feed

  • Question

  • Hi,

    I'm using SQL Server 2005 and Merge Replication with only one Subscriber.

    Recently when I tried to insert data (around 2000 rows) to the Publisher (i.e. database) I got the following error:

    The insert failed. It conflicted with an identity range check constraint in database 'CustomerDatabase', replicated table 'dbo.Customer ', column ' Customer ID'. If the identity column is automatically managed by replication, update the range as follows: for the Publisher, execute sp_adjustpublisheridentityrange; for the Subscriber, run the Distribution Agent or the Merge Agent. The statement has been terminated

    My subscriber hadn’t been run for 3-4 months. Restarting the Merge agent also did not solve my problem.

    My last identity column value was 25000. After reading about the problem, I came to know that the identity range had expired. So I had to allocate new set of identity values.

    After executing the stored procedure "sp_adjustpublisheridentityrange" on table name, my identity value started from 706250. And the next 2000 rows had continuous values starting from 706250. Why there such a big difference?

    At the Publisher end (under Article Properties), my Publisher and Subscriber range for table had 100000 and 15000 values respectively for Customer table with threshold value 80.

    My problems are:

    1. After allocating new set of identity values to 706250, should I had to run the query to reseed from 25001 so that the next identity value was 25001 and not 706250?
    2. Will this problem reappear again? If yes, how can solve this so that it should never ever appear in future?
    3. How do I reproduce this issue?
    4. What would have happened if i had executed the stored procedure "sp_adjustpublisheridentityrange" on the Publisher?
    5. Will the same problem occur if multiple subscribers are used? If yes, how do i solve it?
    6. Can I edit the columns having identity value from 706250 back to starting from 25001..?

    Please suggest me solutions for all the 6 points if its valid...

    Thanks and Regards,

    Sanketh





    Monday, March 11, 2013 12:31 PM

All replies

  • For a publication which has the auto identity range enabled, the Distribution Agent or Merge Agent is responsible for automatically adjusting the identity range in a publication based on its threshold value. However, if for some reason the Distribution Agent or Merge Agent has not been run for a period of time, and identity range resource have been consumed heavily to the point of threshold, users can call sp_adjustpublisheridentityrange to allocate a new range of values for a Publisher. 

    Wednesday, March 13, 2013 6:02 AM
  • Hi,

    I have already executed it. And it worked for me. 

    But how do avoid the same problem if it arises in future? Also how do i reproduce the problem?

    Wednesday, March 13, 2013 1:05 PM