none
Excessive identity range allocations

    Question

  • On one particular subscriber (out of 40+), an error was throw that there were no available identity ranges.  In debugging, it was found that this particular subscriber had allocated ranges for all articles 800+ times over the course of a few days by examining msmerge_identity_range_allocations thereby using all int values.

    I'm trying to figure out why this happened.  Perhaps since all articles for this publication exhibited this issue, the subscription was marked for reinitialization and it was never able to complete but allocated new ranges for the articles and didn't remove those allocations upon not being able to complete all actions.  There do not seem to be any hints in the SQL Error logs to help my debugging process. 

    Has anyone else seen an issue like this?  If so, how did you correct it or at least determine root cause?

    Wednesday, July 24, 2013 7:59 PM

Answers

  • I've seen this happen with Merge Replication over Web Synchronization.  When initializing a subscriber and applying the snapshot there would be an error (access violation) and on the next sync a new identity range would be allocated unnecessarily, over and over, thus exhausting the available identities for the data type.

    I worked with a MS support engineer but they never determined the root cause, they could not repro it.  It was a bug IMO and I'm not sure if it was ever fixed.  You may want to try applying the latest service pack and see if that alleviates the issue.  If not I recommend contacting Microsoft Support, especially if you can reproduce it.


    Brandon Williams (blog | linkedin)

    Thursday, July 25, 2013 12:33 AM
  • Hello,

    Did the issue persists after the latest service pack for the SQL Server 2008? If not, you can try to  submit a feedback  at https://connect.microsoft.com/SQLServer/. Your feedback is valuable for us to improve our products and increase the level of service provided.

    Regards,
    Fanny Liu

    If you have any feedback on our support, please click  here.


    Fanny Liu
    TechNet Community Support

    Saturday, July 27, 2013 2:19 AM

All replies

  • I've seen this happen with Merge Replication over Web Synchronization.  When initializing a subscriber and applying the snapshot there would be an error (access violation) and on the next sync a new identity range would be allocated unnecessarily, over and over, thus exhausting the available identities for the data type.

    I worked with a MS support engineer but they never determined the root cause, they could not repro it.  It was a bug IMO and I'm not sure if it was ever fixed.  You may want to try applying the latest service pack and see if that alleviates the issue.  If not I recommend contacting Microsoft Support, especially if you can reproduce it.


    Brandon Williams (blog | linkedin)

    Thursday, July 25, 2013 12:33 AM
  • Thanks Brandon.  It's interesting that MS could not determine a root cause. 

    We're running SQL 2008 SP2.  I'm going to look at installing SP3 and perhaps CU12.  They were recommended by MS for another replication issue (slowness) that I'm seeing at one particular site (not this one).

    Anyone else have any similar situation?  Thanks in advance.

    Thursday, July 25, 2013 7:05 PM
  • Hello,

    Did the issue persists after the latest service pack for the SQL Server 2008? If not, you can try to  submit a feedback  at https://connect.microsoft.com/SQLServer/. Your feedback is valuable for us to improve our products and increase the level of service provided.

    Regards,
    Fanny Liu

    If you have any feedback on our support, please click  here.


    Fanny Liu
    TechNet Community Support

    Saturday, July 27, 2013 2:19 AM