DBCC checkdb allocation errors

Question

DBCC checkdb allocation errors

Aashini Shah

Ten Centuries

Points: 1129
More actions
June 2, 2014 at 12:08 pm

#307786

Here is the error message from dbcc checkdb.
Msg 8966, Level 16, State 2, Line 1
Unable to read and latch page (1:36994512) with latch type SH. 23(Data error (cyclic redundancy check).) failed.
Msg 8998, Level 16, State 2, Line 1
Page errors on the GAM, SGAM, or PFS pages prevent allocation integrity checks in database ID 6 pages from (1:36994512) to (1:37002599). See other errors for cause.
CHECKDB found 2 allocation errors and 0 consistency errors not associated with any single object.
This is DEV environment and we don't backup DEV. Making long story short, business unit which owns the dbs, they create request to backup db in dev. They forgot. And as with Murphy's law, business unit is extremely concerned now as they have almost 2 weeks worth of test data and configs done on this database.
This is one off dbs that we have not on SAN. So, we try to swap the disk but with no luck ( of course ). Firmware is also checked. One thing we have not done is run DBCC checkdb with repair as everyone is extremely concerned with data loss and so I am not able to get permission to run that. Eventhough error does not look too bad, I am not sure if dbcc will be able to repair it completely.
So, dbas got to have the mammoth task of scripting out everything and copying data from almost 277 tables etc..We copied tables out to different database. now going through gazzillion errors while creating 10000 procs, indexes, keys etc. Is it worth it? To me, data is still lost somewhere that I am not aware. To me, refreshing environment from production seems the best option.
However, we are told to treat this is as production for now and try to get it back clean. Is there any way to find out what is in range: database ID 6 pages from (1:36994512) to (1:37002599)? Is DBCC checkdb with repair allow data loss worth fighting for or focus on rebuild db from scripts?

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Matt Crowley SSCarpal Tunnel Points: 4163 More actions · Answer 1

If repair_allow_data_loss is the least option it is giving you, then you can be fairly certain that some data will be lost. The range you specify has a lot of pages in it, so there could be all sorts of things lost. Worse yet, I doubt the repair algorithm would respect foreign keys, so you could end up with all sorts of hidden problems for later on. The best bet (without a backup) is to do what you guys are already doing, and try to export as much data as you can, and hope that you get it all. Trying to rebuild the stored procedures may give the developers a chance to clean out some of the older stuff that may not be consistent with the table structure as it currently is.

Jon.Morisi SSChampion Points: 12846 More actions · Answer 2

You may find this helpful:

TechEd: 80 minute video of Corruption Survival Techniques presentation[/url]

You may be able to do page restores if you have all the pre-requisite's

Aashini Shah Ten Centuries Points: 1129 More actions · Answer 3

Aashini Shah

Ten Centuries

Points: 1129

June 2, 2014 at 2:21 pm

#1718363

Thanks guys.

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

Can you please run the following and post the full and complete, unedited output?

DBCC CheckDB(<database name here>) with no_infomsgs, all_errormsgs

I can't tell if you've left out a line or not.

Edit: and please don't run anything else in the meantime.

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

Aashini Shah Ten Centuries Points: 1129 More actions · Answer 5

Gail,

Interestingly, we decided to take a chance and turn sql services off. After that we copied the mdfs and ldfs to different server, attached, ran dbcc, it came out clean.

The original server is rebuilt and we have attached it to SAN to avoid this issue in future.

One more thing we messed up is original database was sql server 2008 ( it is 3rd party application ). In rush, we attached it to 2008 r2 instance. Now db is upgraded to 2008 R2 version and we are unable to restore it back to the original server in 2008 compatibility. Always something to learn that makes the job interesting..learnt to slow down, think thrice before taking any action.

As far as DBCC check output, original instance is rebuild.

This were the error messages in the beginning.

Msg 8966, Level 16, State 2, Line 1

Unable to read and latch page (1:36994512) with latch type SH. 23(Data error (cyclic redundancy check).) failed.

Msg 8998, Level 16, State 2, Line 1

Page errors on the GAM, SGAM, or PFS pages prevent allocation integrity checks in database ID 6 pages from (1:36994512) to (1:37002599). See other errors for cause.

CHECKDB found 2 allocation errors and 0 consistency errors not associated with any single object.

for about 2000 tables:

DBCC results for <tableName>

There are x rows in y pages for object <tableName>

At the end:

CHECKDB found 2 allocation errors and 0 consistency errors in database 'tlMain'.

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Thanks,

Aashini

Aashini Shah Ten Centuries Points: 1129 More actions · Answer 6

Also,

I was wrong in my judgement here. I was afraid of taking db offline or attaching/detaching thinking that we will lose it.

Until yesterday I was not aware that this server had local disks. Had I known, I would have opted to copy database files and tried to attach db files to another instance few days earlier considering it a false alarm from disk system and OS..