CHECKDB found 0 allocation errors and 772 consistency errors in database - How serious is this?

Question

CHECKDB found 0 allocation errors and 772 consistency errors in database - How serious is this?

isuckatsql

SSChampion

Points: 10381
More actions
May 28, 2012 at 1:16 pm

#256603

I ran a Check DB and received the following messages.
"CHECKDB found 0 allocation errors and 772 consistency errors in database"
I restored two seperate backups and got the same error, so it was more than a corrupt backup.
What is the typical procedure that i need to follow to fix this?
Thanks
One of the errors:
Object ID 1336821016, index ID 0, partition ID 72057599521456128, alloc unit ID 72057599598854144 (type In-row data): Errors found in off-row data with ID 392167424 owned by data record identified by RID = (1:27277791:6)

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Please run the following and post the full and complete output.

DBCC CHECKDB (<Database Name>) WITH NO_INFOMSGS, ALL_ERRORMSGS

If you don't have a clean backup, fixing this will require losing data, so it's pretty bad (that's assuming it's repairable)

How come you managed to not notice corruption for long enough to have two backups containing it? you are running regular integrity checks and taking backups with Checksum?

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

isuckatsql SSChampion Points: 10381 More actions · Answer 2

I typically run a weekly backup, its not a production DB yet, and i have never had any errors.

Since my backups have never produced any errors, i have never run anything else, other than a rebuild / reorg.

I have been doing backups on this DB for over one year, without any issues.

Thanks for your help!

isuckatsql SSChampion Points: 10381 More actions · Answer 3

Per your request, i have attached an error report.

Thanks

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

Backups aren't integrity checks. http://sqlskills.com/BLOGS/PAUL/post/A-SQL-Server-DBA-myth-a-day-%282730%29-use-BACKUP-WITH-CHECKSUM-to-replace-DBCC-CHECKDB.aspx

Still need the output I asked for...

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

isuckatsql SSChampion Points: 10381 More actions · Answer 5

isuckatsql

SSChampion

Points: 10381

May 28, 2012 at 5:29 pm

#1493931

I just attached the report.

Thanks

DBA328 Hall of Fame Points: 3952 More actions · Answer 6

Found this: repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB

Becuase repair_allow_data_loss could make data lost, so it is better to restore from an old backup then export data to this database. After that run DBCC CHECKDB again.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 7

If you run checkDB with repair you will lose data from the following tables

'AllJobTitles'

'AllDocuments'

'EmailAddressVIADB'

This data loss cannot be avoided unless you can find and restore a clean backup.

You also seriously need to do some investigation of the IO subsystem on this server, there's a lot of corruption in this database and that is almost certainly something wrong somewhere with the IO subsystem.

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

isuckatsql SSChampion Points: 10381 More actions · Answer 8

I did have some recent issues with either my RAID controller, backplane or both, so Dell replaced them both.

I will check another copy of the DB running on a different server, to see if that is also corrupt!

Thanks for your help on this !

isuckatsql SSChampion Points: 10381 More actions · Answer 9

Update:

My DB copy on my other server is fine, no errors 🙂

So it is looking like it was the RAID controller or backplane issue !