Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

"The identity range managed by replication is full and must be updated by a replication agent."

This has started happening. We recently broke replication on purpose and don't plan to re-establish the replication.

What else do I need to do to fix this error? Does anyone have any advice for me?

share|improve this question

1 Answer 1

up vote 1 down vote accepted

MS SQL Server replication uses database constraints on primary key columns to prevent collisions between servers.

For example, let's say you have a table called "users" with a primary key called "user_id" running on server A and server B. Server A will have a constraint on the user_id column permitting values in the range 1 to 100. Server B will have a constraint on the user_id column permitting values in the range 101 to 200. This way, if a new user is inserted at the same time on server A, and meanwhile a different new user is inserted on server B, they will never have the same user_id number.

Since you broke replication intentionally and don't plan to start it up again, you have two options. (Standard disclaimer: make a backup.)

If you haven't already, you could try deleting the publication from within SQL Server Management Studio. That should remove all of the replication configuration stuff from your database and may fix the issue for you.

Otherwise, if you need to do it manually, you should be OK to go in and delete these constraints by hand. Go to the table in question, drill down to constraints, and look for one(s) named something similar to "repl_identity_range". Right-click on it and select "Modify". You should see an expression limiting the identity range. It will look something like this:

([user_id]]>(1) AND [user_id]]<=(100))

This is the actual rule that makes sure that newly generated ids are unique to each server. Delete it and you should stop seeing these errors.

Here are some other resources that might be helpful/informational:

http://www.simple-talk.com/sql/database-administration/the-identity-crisis-in-replication/ http://msdn.microsoft.com/en-us/library/ms176057.aspx

share|improve this answer
    
I went in and first removed all the "articles" from the publication, one by one (120 tables). I probably didn't need to do that, but whatever. I then deleted the publication altogether, and it all seems to work perfectly so far. Thanks for the help and pointers, Deutsch. –  Matt Dawdy Mar 26 '10 at 20:40

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.