SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Logical Consistency Error


Logical Consistency Error

Author
Message
Dave Mason
Dave Mason
SSC Eights!
SSC Eights! (814 reputation)SSC Eights! (814 reputation)SSC Eights! (814 reputation)SSC Eights! (814 reputation)SSC Eights! (814 reputation)SSC Eights! (814 reputation)SSC Eights! (814 reputation)SSC Eights! (814 reputation)

Group: General Forum Members
Points: 814 Visits: 917
It seems that I have some corruption within a user database. Running DBCC CHECKDB results in the following error:
Msg 8921, Level 16, State 1, Line 1
Check terminated. A failure was detected while collecting facts. Possibly tempdb out of space or a system table is inconsistent. Check previous errors.


I tried running DBCC CHECKALLOC and DBCC CHECKCATALOG. Both complete without an error. Next, I ran DBCC CHECKTABLE on each table. I found a single table that produced the same error as above from DBCC CHECKDB.

When I try to query the table (for instance, SELECT COUNT(*) FROM dbo.Table), SSMS returns this error (which is identical to the corresponding errors in the SQL Server error log and the system event log):
Msg 824, Level 24, State 2, Line 1
SQL Server detected a logical consistency-based I/O error: incorrect pageid (expected 1:98530; actual 57553:769). It occurred during a read of page (1:98530) in database ID 26 at offset 0x000000301c4000 in file 'D:\Program Files\Microsoft SQL Server\MSSQL10.ATLCSETASQL\MSSQL\DATA\xt3_newbraunfels_01.mdf'. Additional messages in the SQL Server error log or system event log may provide more detail. This is a severe error condition that threatens database integrity and must be corrected immediately. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online.


Next, I took the info from above and ran this:

DBCC TRACEON (3604)
DECLARE @DBId INT = DB_ID(DB_Name())
DBCC PAGE( @DBId, 1, 98530 )



I'm trying to interpret the output from DBCC PAGE (see attached). However, I'm not sure how to proceed.

Dave MasonSeminole County, FL
Attachments
DBCC Page Output.txt (14 views, 1.00 KB)
Jack Corbett
  Jack Corbett
SSC-Forever
SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)

Group: General Forum Members
Points: 42149 Visits: 14925
The best way to recover from this is to restore from a good backup.



Jack Corbett

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming
At best you can say that one job may be more secure than another, but total job security is an illusion. -- Rod at work

Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
GilaMonster
GilaMonster
SSC Guru
SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)

Group: General Forum Members
Points: 211935 Visits: 46259
Dave Mason (5/29/2014)
It seems that I have some corruption within a user database. Running DBCC CHECKDB results in the following error:
Msg 8921, Level 16, State 1, Line 1
Check terminated. A failure was detected while collecting facts. Possibly tempdb out of space or a system table is inconsistent. Check previous errors.


Restore from backup.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


Dave Mason
Dave Mason
SSC Eights!
SSC Eights! (814 reputation)SSC Eights! (814 reputation)SSC Eights! (814 reputation)SSC Eights! (814 reputation)SSC Eights! (814 reputation)SSC Eights! (814 reputation)SSC Eights! (814 reputation)SSC Eights! (814 reputation)

Group: General Forum Members
Points: 814 Visits: 917
Thanks Jack and Gail. On May 10th and May 17th, I took full backups. DBCC CHECKDB completed with no errors on the 10th. The logical consistency error showed up on May 17th. I'm thinking I could restore the May 10th backup to a temp db name, truncate (or drop) the offending table in the current db, and rebuild it with data from the backup. Do you think this strategy is worth trying? Or is it strictly an all-or-nothing problem?

Dave MasonSeminole County, FL
GilaMonster
GilaMonster
SSC Guru
SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)

Group: General Forum Members
Points: 211935 Visits: 46259
Restore the database from a clean backup, before the corruption occurred. You have some corruption more sever than just that table or checkDB wouldn't have aborted with an error

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


Jack Corbett
  Jack Corbett
SSC-Forever
SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)

Group: General Forum Members
Points: 42149 Visits: 14925
I don't know enough about the database to know if your data would be in a consistent state if you restored to another database name and rebuilt the table from the almost 3 week old backup. If there is enough data that would have to be re-entered or lost it might be worth a shot. If there isn't a lot of work created by just doing a restore and starting over from that point I'd go with that.

You should also be checking your storage to make sure it doesn't happen again. Usually there are hardware issues when a database gets corrupted.



Jack Corbett

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming
At best you can say that one job may be more secure than another, but total job security is an illusion. -- Rod at work

Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
Dave Mason
Dave Mason
SSC Eights!
SSC Eights! (814 reputation)SSC Eights! (814 reputation)SSC Eights! (814 reputation)SSC Eights! (814 reputation)SSC Eights! (814 reputation)SSC Eights! (814 reputation)SSC Eights! (814 reputation)SSC Eights! (814 reputation)

Group: General Forum Members
Points: 814 Visits: 917
I restored the May 10th backup. DBCC CHECKDB still fails. I double-checked the SQL Server Agent job history. The May 10th job that created the full backup and ran DBCC CHECKDB succeeded.

I'm at a loss...

Dave MasonSeminole County, FL
GilaMonster
GilaMonster
SSC Guru
SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)

Group: General Forum Members
Points: 211935 Visits: 46259
Did you ever test the backup after it was made?
Restore it somewhere else and check as well.

Also, make sure you restored the right backup in the right place. :-)

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search