Cannot Make Inserts at Subscribers

I'm getting this error message when trying to insert a row on some
tables in my database.
The identity range managed by replication is full and must be updated
by a replication agent. The INSERT conflict occurred in database 'XXX',
table 'tblContact', column 'Number'. Sp_adjustpublisheridentityrange can
be called to get a new identity range.
These subscribers have been running for several months with out issue
and they are now all getting these errors as of yesterday. Running the
merge agent does not remedy the problem and that stored procedure seems
to only work for the publisher which is running just fine.
Running dbcc checkident ('tblContact', NORESEED) returns
Checking identity information: current identity value '94005', current
column value '94005'.
This will increase by one if I try an insert, but the insert fails.
I took everything down. Deleted the databases on the subscribers and
disabled publishing on the publisher, and started from scratch.
After setting up the replication again, some of the tables that were
giving me the problem are now working, but the majority of the tables
are still giving me this error.
I've run out of things to try. I would appreciate any further
suggestions.
Thank you,
David
Basically you have blown your identity range.
http://www.simple-talk.com/sql/database-administration/the-identity-crisis-in-replication/
Automatic identity range management has a check constraint which constrains
the possible identity values which can be used on your publisher and
subscriber. If you do an insert the check constraint will kick back the
insert when you blow the range and increment the current identity value.
the proc Sp_adjustpublisheridentityrange will adjust the identity range on
the Publisher, but not the subscriber. To fix this on your subscribers you
have to sample each one and figure out where they are. Then run the merge
agent one by one. One of the first things it will do when it detects the
range has been blown is to adjust it.
If this fails to work you may have to manually intervene and reset it based
on what all your subscribers are at and the values stored in select * from
distribution.dbo.MSmerge_identity_range_allocation s
You may also need to run your merge agents more frequently or bump up the
ranges to account for the max amount of data inserted on the subscriber at
any one time between syncs.
Hilary Cotter
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
"David TMS" <IdontlikeSpam@.nospam.com> wrote in message
news:MPG.20636a6d184a3e8a989680@.msnews.microsoft.c om...
> I'm getting this error message when trying to insert a row on some
> tables in my database.
> The identity range managed by replication is full and must be updated
> by a replication agent. The INSERT conflict occurred in database 'XXX',
> table 'tblContact', column 'Number'. Sp_adjustpublisheridentityrange can
> be called to get a new identity range.
> These subscribers have been running for several months with out issue
> and they are now all getting these errors as of yesterday. Running the
> merge agent does not remedy the problem and that stored procedure seems
> to only work for the publisher which is running just fine.
> Running dbcc checkident ('tblContact', NORESEED) returns
> Checking identity information: current identity value '94005', current
> column value '94005'.
> This will increase by one if I try an insert, but the insert fails.
> I took everything down. Deleted the databases on the subscribers and
> disabled publishing on the publisher, and started from scratch.
> After setting up the replication again, some of the tables that were
> giving me the problem are now working, but the majority of the tables
> are still giving me this error.
> I've run out of things to try. I would appreciate any further
> suggestions.
> Thank you,
> David
|||Running the merge agent did not fix this problem for me. The identity
ranges on the subscribers were also more than large enough. They had
room for 10,000 inserts on each table and were not able to make any on
most of the tables, but other tables seemed to work just fine. Deleting
the publication and disabling replication, but keeping the original
database seemed to make the problem recur immediately after setting up
the replication. I'm still not sure of the cause of this problem, but I
seem to have a work around. I started a new database with a fresh
schema. I then imported the data from the old database. I set
everything back up using this new database name and it went fairly
smoothly.
Thanks,
Dave
In article <#2pv#P8ZHHA.4720@.TK2MSFTNGP06.phx.gbl>,
hilary.cotter@.gmail.com says...
> Basically you have blown your identity range.
> http://www.simple-talk.com/sql/database-administration/the-identity-crisis-in-replication/
> Automatic identity range management has a check constraint which constrains
> the possible identity values which can be used on your publisher and
> subscriber. If you do an insert the check constraint will kick back the
> insert when you blow the range and increment the current identity value.
> the proc Sp_adjustpublisheridentityrange will adjust the identity range on
> the Publisher, but not the subscriber. To fix this on your subscribers you
> have to sample each one and figure out where they are. Then run the merge
> agent one by one. One of the first things it will do when it detects the
> range has been blown is to adjust it.
> If this fails to work you may have to manually intervene and reset it based
> on what all your subscribers are at and the values stored in select * from
> distribution.dbo.MSmerge_identity_range_allocation s
> You may also need to run your merge agents more frequently or bump up the
> ranges to account for the max amount of data inserted on the subscriber at
> any one time between syncs.
>