SQL Server – Is there a formula for determining what identity range to set?

Tags:
SQL Server
SQL Server errors
We have been struggling with exhausting our range and get the following error: 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 identity (Source: MSSQL_REPL, Error number: MSSQL_REPL-2147199417). Get help: http://help/MSSQL_REPL-2147199417 Not enough range available to allocate a new range for a subscriber. (Source: MSSQLServer, Error number: 20668) We have a Publication that has 9 subscribers who make thousands of record changes daily and have run in to the identity range problem. Thanks for your help...

Answer Wiki

Thanks. We'll let you know when a new response is added.

The easiest formula is how many records will each subscriber add into the database between syncs. Take that number and multiply by 5. That should give you plenty of identity values for a decent amount of time if the systems don’t replicate for a couple of cycles. Of course if you replicate every hour you will want to increase the number that you multiply by to say 24 or 48 to cover a couple of day window of outage.

You don’t want to go two crazy with the number or you will have so many values left in the range that when you replicate SQL will not realize that you need more and will not assign a new range to the subscriber and you’ll end up running out of IDs again.

Discuss This Question: 3  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • Denny Cherry
    Check out my SQL Server blog "SQL Server with Mr Denny" for more SQL Server information.
    66,130 pointsBadges:
    report
  • JoATMoN DJT
    Alternately you can change the identity seed and increment vaules so that no manual intervention is required until you change the number of subscribers. See:
    report
  • JoATMoN DJT
    Alternately you can change the identity seed and increment vaules so that no manual intervention is required until you change the number of subscribers. Like so: http://www.quest-pipelines.com/newsletter-v4/0903_F.htm
    20 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following