Server Fault is a question and answer site for system and network administrators. It only takes a minute to sign up.
Sign up to join this community
"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?
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
