February 4, 2010 at 12:38 am
I have just set up some log shipping in preparation for a server upgrade. Both the source and target instances are running SQL 2000 SP4.
When the logs are restored I am getting the following warning, but only on intermittent restores:
Recovery of database 'MyDBName' (7) detected possible identity value inconsistency in table ID 1839345617. Run DBCC CHECKIDENT ('MyTableName').
So most log backups are restored fine, but sometimes I get the above error.
Does anyone know if this is a particular cause for concern? I can't run DBCC CHECKIDENT because the log-shipped db is read-only.
Incidentally, I received this same error for the first time recently after restoring a differential backup on an entirely separate database (again SQL 2000). After I got that, I ran DBCC CHECKIDENT on the table in question and it returned the expected result. I then ran a full DBCC CHECKDB and it returned no errors. I have not experienced any issues with the restored db.
-----
JL
February 6, 2010 at 12:30 pm
It's not the data portion of the restore, it's the replay of the transaction log in the restore that's hitting an identity problem. This can happen if a value gets corrupted in the database or in the backup - most likely the backup otherwise you'd have seen the error in production.
DBCC CHECKDB doesn't check identity values - that's what DBCC CHECKIDENT does. Why can't you run it on a R/O database? Only if you're trying to RESEED should it fail.
Thanks
Paul Randal
CEO, SQLskills.com: Check out SQLskills online training!
Blog:www.SQLskills.com/blogs/paul Twitter: @PaulRandal
SQL MVP, Microsoft RD, Contributing Editor of TechNet Magazine
Author of DBCC CHECKDB/repair (and other Storage Engine) code of SQL Server 2005
February 6, 2010 at 12:51 pm
I've actually brought the db online now and CHECKIDENT is not showing anything unexpected. CHECKDB didn't return any errors either.
Not sure what exactly caused errors but db and table appear fine.
Thanks for the response anyway.
-----
JL
February 19, 2010 at 4:45 am
Just in case anyone finds this post later on, when I tried to run
DBCC CHECKIDENT(MyTableName, NORESEED)
while the database was in standby (read only) I got the following error:
Msg 3906, Level 16, State 1, Line 2
Could not run BEGIN TRANSACTION in database 'MyDbName' because the database is read-only.
-----
JL
February 19, 2010 at 3:21 pm
But that is expected behavior - the RESEED has to update the system catalogs to reflect the new next-identity value - which it can't do on a read-only databaase.
Paul Randal
CEO, SQLskills.com: Check out SQLskills online training!
Blog:www.SQLskills.com/blogs/paul Twitter: @PaulRandal
SQL MVP, Microsoft RD, Contributing Editor of TechNet Magazine
Author of DBCC CHECKDB/repair (and other Storage Engine) code of SQL Server 2005
February 20, 2010 at 2:06 am
Agreed, but I was still getting the error using NORESEED, as per my post. I'm guessing the DBCC code does a BEGIN TRAN whether you want it to reseed or not, but I guess you'd know better than me! 😉
-----
JL
February 20, 2010 at 10:41 am
Hmm - it works perfectly well for me on an SP3 system...
create database foo
go
use foo
go
create table t1 (c1 int identity, c2 int)
go
insert into t1 values (1)
insert into t1 values (2)
go
alter database foo set read_only
go
dbcc checkident ('t1', NORESEED)
go
Paul Randal
CEO, SQLskills.com: Check out SQLskills online training!
Blog:www.SQLskills.com/blogs/paul Twitter: @PaulRandal
SQL MVP, Microsoft RD, Contributing Editor of TechNet Magazine
Author of DBCC CHECKDB/repair (and other Storage Engine) code of SQL Server 2005
February 22, 2010 at 8:57 am
Looks like this was a code change in 2005; works fine in 2005/2008 but not in 2000, which unfortunately is what we're using here.
-----
JL
February 22, 2010 at 9:08 am
Ah - forgot we were in the 7.0/2000 forum. Indeed - I rewrote most of that code for 2005 - removing all kinds of weird behavior.
No workaround I'm afraid, except the obvious one of bringing the DB into read/write.
Apologies for any confusion James.
Cheers
Paul Randal
CEO, SQLskills.com: Check out SQLskills online training!
Blog:www.SQLskills.com/blogs/paul Twitter: @PaulRandal
SQL MVP, Microsoft RD, Contributing Editor of TechNet Magazine
Author of DBCC CHECKDB/repair (and other Storage Engine) code of SQL Server 2005
February 22, 2010 at 9:18 am
No probs, thanks for your input. The database in question has been online for a couple of weeks now and no sign of any issues; I just thought I'd add the error info for anyone's future reference.
-----
JL
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply