Odd Identity Range/Constraint Violations

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

btysgtmajor@gmail.com - 27 Jul 2006 18:00 GMT

Hi there,

I'm still fairly new to replication on SQL Server 2000, but, I'm
quickly learning (trial by fire). I have a database that has a
publication with six subscribers. A couple of the tables have their
identity ranges auto-managed (the IDENTITY column in each of the tables
is marked NFR, as are all constraints, relationships, etc.).
Occasionally, I'll get an error saying an ID range is full and that the
merge agent has to change it or I can run sp_adjustpublisheridentity
range. The thing is, when I run DBCC CHECKIDENT, it says the values
are just fine.

After much searching, I looked at the actual identity constraint
created by SQL Server. The ID constraint's range was way below what it
should have been, hence the cause of my problem. Now, this constraint
is marked NFR and the merge agent hasn't had a problem and runs once
every 4 minutes. The assigned range and threshold are adequate (range
= 10 000, threshold = 80%). I looked in the articles' properties and
"referential integrity" is one of the copeid schema objects (which I
can only assume is for the constraint above as I can see no other
explicit wording of it).

I'm confused as to why the merge agent isn't updating the constraint.
Mind you, I only get the error when someone/something other than the
merge agent (e.g. the system I coded that makes use of the DB) tries to
INSERT. Does anyone have any ideas/suggestions? Thanks in advance for
your help.

Cheers,
Duncan

Reply to this Message

Hilary Cotter - 29 Jul 2006 01:56 GMT

Something doesn't sound right here. DBCC CheckIdent should reveal the
current range and the next value it will hand out. For example running this
on the jobs table in the publication database shows this:

Checking identity information: current identity value '14', current column
value '14'.
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.

With automatic identity range management when you blow the range the
constraint will kick back the insert, but the identity value will be
incremented, so if you blow the range on the jobs table and insert 100 rows
it will look like this

Checking identity information: current identity value '14', current column
value '114'.
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.

as you have insert 100 more rows, the identity value was updated 100 times,
but all were kicked back.

If merge replication assigns a new range it will update the constraint. Are
you running continuously? You should schedule it. If you bump into this
condition too often, sync more frequently.

Have a look at this article for more info on identity range management.

http://www.simple-talk.com/sql/database-administration/the-identity-crisis-in-re
plication/

Signature

Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.

This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.

Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html

Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com

> Hi there,
>
[quoted text clipped - 26 lines]
> Cheers,
> Duncan

Reply to this Message

btysgtmajor@gmail.com - 29 Jul 2006 17:20 GMT

CheckIdent was returning that the next value to be handed out would be
just fine, and the system wasn't trying to insert more than one or two
rows to the table affected. Also, as I said before, this merge agent
is scheduled to run every four minutes. What really confuses me is why
the current range for the given subscriber's affected table was above
that of the constraint (and, as I said, the constraint is marked NFR).
I know that the replication was just fine, but anything else trying to
insert a row into the table (via the system I created) would violate
the constraint. Honestly, to me, it looks like the constraint wasn't
updated on synchronization. Otherwise, the agent is working quite
well. I'm worried about adjusting the constraint by hand, though.

> Something doesn't sound right here. DBCC CheckIdent should reveal the
> current range and the next value it will hand out. For example running this
[quoted text clipped - 70 lines]
> > Cheers,
> > Duncan

Reply to this Message

Hilary Cotter - 30 Jul 2006 11:11 GMT

You can adjust it by hand just make sure that the range you pick is not in
use or about to be handed out.

Signature

> CheckIdent was returning that the next value to be handed out would be
> just fine, and the system wasn't trying to insert more than one or two
[quoted text clipped - 88 lines]
>> > Cheers,
>> > Duncan

Reply to this Message