| Groups | Blog | Home |
sql server replication : Odd Identity Range/Constraint Violations
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
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-replication/ -- 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 [quoted text, click to view] <btysgtmajor@gmail.com> wrote in message news:1154019648.646550.308310@h48g2000cwc.googlegroups.com... > 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 >
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. [quoted text, click to view] Hilary Cotter wrote:
> 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-replication/ > > > > -- > 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 > > > > <btysgtmajor@gmail.com> wrote in message > news:1154019648.646550.308310@h48g2000cwc.googlegroups.com... > > 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 > >
You can adjust it by hand just make sure that the range you pick is not in
use or about to be handed out. -- 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 [quoted text, click to view] <btysgtmajor@gmail.com> wrote in message news:1154190007.345969.204070@b28g2000cwb.googlegroups.com... > 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. > > > Hilary Cotter wrote: >> 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-replication/ >> >> >> >> -- >> 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 >> >> >> >> <btysgtmajor@gmail.com> wrote in message >> news:1154019648.646550.308310@h48g2000cwc.googlegroups.com... >> > 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 >> > >
Don't see what you're looking for? Try a search.
|
April 2003
June 2003
July 2003
August 2003
September 2003
October 2003
November 2003
December 2003
January 2004
February 2004
March 2004
April 2004
May 2004
June 2004
July 2004
August 2004
September 2004
October 2004
November 2004
December 2004
January 2005
February 2005
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
March 2006
April 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007
November 2007
October 2008
| home · blog · groups | Questions? Comments? Contact the dn |