Monday, March 19, 2012

Identity Seed gone bad in merge replication

Sever: sql 2000
Replication: Merge

Issue:
I am having an issue with my audit table, This table is filled by Triggers on various tables through the database. All triggers are defied with "not for replication"

I have allocated 500k ranges, with 80% threshold to the publisher and subscriber databases for this table. The table only holds 225,000 records.

From time to time I get the following error "The identity range managed by replication is full and must be updated by a replication agent. The INSERT conflict occurred in database 'PublicationName', table 'AuditHistory', column 'AuditID'. Sp_adjustpublisheridentityrange can be called to get a new identity range."

When I looked into the issue yesterday I noticed that the identity range being used by replication was 334300001 -> 334799999, however the maximum value in the table was 334300096, meaning that only 95 records were inserted, which means it is no where near the 80% threshold.

Somehow the identity seed on the AuditHistory table had been changed to 334800104, which is outside the allowable range.

My question is what could cause the identity seed to get set to such a high number??

any thoughts would be great!Identity values are used up even if the insert is rolled back. I doubt you had 490,000 rollbacks, though.|||I agree, we also use hibernate in the weblayer if that makes any difference!

No comments:

Post a Comment