The identity range managed by replication is full... - help?!
-
Here's my setup, I have a merge publication, which is replicated via a distribution server to a subscriber on another site. I have a table in thei publication with a managed identity, blocks of a 1000, with 80% threshold. I have clients accessing the publication database directly and the subscribers database.
I have an issue when inserting new rows into a table at the publisher, saying the range is full. The last row has an id of 85999, if I run 'dbcc checkident' I get: current identity value '86018', current column value '86016'. The subscriber has: current identity value '87001', current column value '87001'.
I've tried sync'ing up, I've tried sp_adjustpublisheridentityrange, nothing much seems to have an effect. I do seem to have got a new identity range, judging by the results from dbcc checkident, but its as if some constaint hasn't been updated. Any ideas?
Thanks,
Arthur
-
Ok, think I found the problem, I had a rouge old check constraint in place, so when I ran sp_help on the table, I had these two rows:-
CHECK on column Ver_IDrepl_identity_range_pub_2B562400_FE8A_456F_9C10_DC0637BE074E(n/a)(n/a)EnabledNot_For_Replication([Ver_ID] > 84398 and [Ver_ID] 87000 and [Ver_ID] < 88000)
The former, had also been replicated to the subscriber too... Feck knows what was going on there, eitherway I've deleted the first, seem to be ok now. Hope I've done the right thing and not caused any other problems.
Arthur
-
Arthur,
You can probably rest easy. I've done the same thing in the past without negative repurcussions.
The code that updates the CHECK constraints for publisher-side identity ranges will only delete 1 check constraint ever. I think it may be a bug. (heh) So running the sp_adjustpublisheridentityrange won't fix your problem all by itself.
I don't know what could cause it to get out of whack, but when it does, you've got to get rid of the incorrect constraints.
Congrats on finding it so quickly! The error message doesn't actually say anything about a constraint violation....
jg
-
Yeah, no idea what caused two constraints to crop up. Eitherway, I just figured it was using a constraint to implement the range checking. I also found a thread on google somewhere, saying to run sp_help to confirm everything was correct about the table.
-
Another simple method to handle this is to reseed the identity values, using DBCC CHECKIDENT.
These types of situation could happen when a transactions which begin did not rollback completely (programming issue).
rangark
-
I'm pretty sure the identity seed was ok and didn't need reseeding. The info returned by 'dbcc checkident noreseed', showed this. The error I was getting was due to the id generated, although in a nice new identity range, couldn't be used on a new row insert as the check constraint was stopping it being inserted.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply