DBCC Checkdb Errors, help needed!!

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

Data Corruption Issues

DBCC Checkdb Errors, help needed!!

Author

Topic

BigProb
Starting Member

6 Posts

Posted - 2009-09-23 : 19:43:03

Our application is unable to access only a few records (150) and I am unable to access these records in the db using a query and including only the UniqueID associated with these records. I am able to access these records if I use name and < than a larger UniqueID in the query. Below is the DBCC Checkdb (mydb) errors only. I began to research this due to torn page error messages on page (1:271726) as mentioned below. Changed the page verify to checksum as some has suggested but the application and query to access these specific records continues to error. Please help and it is greatly appreciated!!!

Msg 8928, Level 16, State 1, Line 1
Object ID 171915734, index ID 22, partition ID 72057594047430656, alloc unit ID 72057594051887104 (type In-row data): Page (1:271726) could not be processed. See other errors for details.
Msg 8939, Level 16, State 98, Line 1
Table error: Object ID 171915734, index ID 22, partition ID 72057594047430656, alloc unit ID 72057594051887104 (type In-row data), page (1:271726). Test (IS_OFF (BUF_IOERR, pBUF->bstat)) failed. Values are 29493513 and -1.
Msg 8928, Level 16, State 1, Line 1
Object ID 171915734, index ID 22, partition ID 72057594047430656, alloc unit ID 72057594051887104 (type In-row data): Page (1:271727) could not be processed. See other errors for details.
Msg 8941, Level 16, State 1, Line 1
Table error: Object ID 171915734, index ID 22, partition ID 72057594047430656, alloc unit ID 72057594051887104 (type In-row data), page (1:271727). Test (sorted [i].offset >= PAGEHEADSIZE) failed. Slot 196, offset 0x32 is invalid.
Msg 8942, Level 16, State 1, Line 1
Table error: Object ID 171915734, index ID 22, partition ID 72057594047430656, alloc unit ID 72057594051887104 (type In-row data), page (1:271727). Test (sorted[i].offset >= max) failed. Slot 0, offset 0xc4 overlaps with the prior row.

CHECKDB found 0 allocation errors and 5 consistency errors in database 'mydb'.
repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB (mydb).

Just concerned more data than just the specific records mentioned above will be affected before running repair_allow_data_loss. As this IS the only backup of the database. I can create a new backup but I would presume the error to still be present just in a new database copy. Right?

ST

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2009-09-24 : 01:23:10

Yes, any backup you take now will have the corruptions in it.

I'm not 100% sure how much data will be lost. Usually checkDB just deallocates entire pages. I have asked for a second opinion on this. I'm also not sure if you will actually lose any data at all, seeing as the corruptions are in a nonclustered index (index id 22)

Can you restore the backup to a test server, run repair there and see how much you lose?

--
Gail Shaw
SQL Server MVP

paulrandal
Yak with Vast SQL Skills

899 Posts

Posted - 2009-09-24 : 01:25:04

This error is only affecting a nonclustered index. The minimum repair level is REPAIR_ALLOW_DATA_LOSS because CHECKDB wants to deallocate the broken page before rebuilding the nonclustered index, and as a rule it will only do that under REPAIR_ALLOW_DATA_LOSS - just in case the information on the page is invalid and the page wasn't really from a nonclustered index.

Instead of running repair, go ahead and drop then recreate that index (rebuilding it most likely won't work, as the rebuild may try to read the old index and trip over the corrupt page).

Now - you said you had a torn-page - you need to figure out why that happened and take corrective action so it doesn't happen again. Changing to page checksums won't ever fix a corruption, it will only help detect future corruptions more easily.

Thanks

Paul S. Randal,
Managing Director, SQLskills.com
Blog: www.SQLskills.com/blogs/paul Twitter: twitter.com/PaulRandal
SQL MVP, Microsoft RD, Contributing Editor of TechNet Magazine
Author of SQL 2005 DBCC CHECKDB/repair code

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2009-09-24 : 03:57:25

Interesting.....

BigProb, may I also suggest, in addition to the root cause analysis that Paul's suggesting, make sure you're doing regular DB integrity checks so that if this happens again you'll be able to pick it up before all of your backups have the corruption in them.

--
Gail Shaw
SQL Server MVP

BigProb
Starting Member

6 Posts

Posted - 2009-09-24 : 07:57:03

Thanks guys for the response. I will try both scenarios but wanted to know though the table has been identified how do I identify the specific index to rebuild.

ST

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2009-09-24 : 08:24:20

SELECT name from sys.indexes where object_id = 171915734 and index_id = 22

--
Gail Shaw
SQL Server MVP

BigProb
Starting Member

6 Posts

Posted - 2009-09-24 : 08:32:58

Thanks GilaMonster I'll try that and post back.

ST

BigProb
Starting Member

6 Posts

Posted - 2009-09-24 : 09:28:21

Thanks guys this data is accessible now!!!

Paul or GilaMonster- The torn page error led me to dbcc checkdb (mydb) which then displayed the errors mentioned here which is resolved now. Do you suggest changing the page verify back to torn page to confirm or should I leave it at checksum? or should I do another dbcc checkdb (mydb)?

Thanks again!!!

ST

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2009-09-24 : 10:48:45

Leave it at checksum. Checksum detects far more problems than torn page can. (though pages will only get the checksums added when they're next modified)
Torn page = default on SQL 2000 and lower. Checksum = new in SQL 2005 and default on DBs created in SQL 2005+.

No harm in doing another checkDB to make sure everything's clean. Have you done some root-cause analysis? Scheduled regular DB integrity checks?

--
Gail Shaw
SQL Server MVP

BigProb
Starting Member

6 Posts

Posted - 2009-09-24 : 13:11:10

I have not done in additional checks but will do so after running another checkdb.

What do suggest as a db integrity check?

ST

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2009-09-25 : 02:56:28

DB integrity check = DBCC CheckDB. It needs to be run often enough that restoring from backup is an option if you have corruption. You can write a job or you can use the maintenance plan with the Check Database Integrity task.

--
Gail Shaw
SQL Server MVP

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources