Help, my database is corrupt. Now what?

Question

Help, my database is corrupt. Now what?

Viewing 15 posts - 61 through 75 (of 86 total)

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

Kangana Beri SSCrazy Points: 2134 More actions · Answer 1

Great article! We had 2 instances of data corruption in last 3 weeks and so here I am...

The first instance was - "Unable to find index entry in index ID 4, of table 1211151360, in database 'dbname'. The indicated index is corrupt or there is a problem with the current update plan. Run DBCC CHECKDB or DBCC CHECKTABLE. If the problem persists, contact product support."

Since it was a non-clustered index, I fixed it by dropping and recreating the index.

The second instance - "Attempt to fetch logical page (1:2081816) in database 5 failed. It belongs to allocation unit 72057602053242880 not to 72057603070820352." The error occured during execution of a stored procedure and it was easy to find the problem query. The procedure creates a table A, inserts somes data in it by selecting from another table B, and then performs a update operation on the same table A. The error occured during the update. The fix was simple - I dropped and recreated the table A, insert the data from table B again and run the same update which succeeded this time.

In both situations, the fixes were faster than running DBCC Checkdb and that is why I choose to do them. We do have regular DBCC CheckDB running.

My question is: is it possible to prevent these errors from happening? In my situation- in the 2nd instance, the corruption occured soon after the table was created - was there any way of avoiding it? The article mentions that I/O subsystems are usually the reason behind corruptions. Anything specific that we should be checking on the I/O subsystem?

Thanks!

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

KB - 2000 (8/22/2011)
Anything specific that we should be checking on the I/O subsystem?

Everything.

It could be anything in the IO stack, from anti-virus right down to the disks.

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

Kangana Beri SSCrazy Points: 2134 More actions · Answer 3

Kangana Beri

SSCrazy

Points: 2134

August 22, 2011 at 1:55 pm

#1371673

Thanks Gail!

We will check and see what we find!

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

btw, if that were my DB, I'd be trying to get it onto alternate storage first, before something worse corrupts.

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

Kangana Beri SSCrazy Points: 2134 More actions · Answer 5

Thanks Gail! Well, alternate storage is probably not an option for us because of budget reasons.

90% of objects in this DB can be dropped and recreated and repopulated within a 3 hr window(which is acceptable to users), the other 10% that can't be are copied over in a separate database everyday and that database is backed up.

I have another question - any chances that DBCC CheckDB can fail to find corruption in user databases?

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

No. If there's corruption, it'll find it in just about all cases.

There are odd cases of single-bit corruption of data within a row where the page doesn't have checksum protection, but that's really unusual.

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

ThomG Old Hand Points: 319 More actions · Answer 7

ThomG

Old Hand

Points: 319

January 11, 2012 at 11:23 am

#1432009

Thank you for the information.

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

Please either post a thread in the forums here (this is for discussion of the article, not fixing of a problem), or post a thread over at SQLTeam.

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

raj k SSC Enthusiast Points: 131 More actions · Answer 9

Great article.

Does a successful backup of database imply no corruptions in the Database?

Ol'SureHand SSCrazy Points: 2744 More actions · Answer 10

raj k (2/8/2012)
Great article.
Does a successful backup of database imply no corruptions in the Database?

No.

See Phil Factor at http://www.simple-talk.com/sql/database-administration/confessions-of-a-dba-my-worst-mistake/%5B/url%5D

raj k SSC Enthusiast Points: 131 More actions · Answer 11

raj k

SSC Enthusiast

Points: 131

February 8, 2012 at 11:00 pm

#1444868

thank you for the link posted

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

raj k (2/8/2012)
Great article.
Does a successful backup of database imply no corruptions in the Database?

Not at all.

If you're doing a normal backup (without checksum), then nothing is checked by the backup. If you do a backup with checksum, then the page checksums are recalculated and checked. The backup will fail if any pages with invalid checksums are found (only of use if the database is using checksums for page verify)

That said, the only way to be completely sure a DB is free of corruptions is to run CheckDB.

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

kellyj481 Grasshopper Points: 15 More actions · Answer 13

Here’s a great tool that lets you build any type of database apps for web and mobile fast and without coding http://www.caspio.com/

quackhandle1975 SSChampion Points: 11055 More actions · Answer 14

Added to the briefcase, nice insurance policy when the worst happens. :w00t:

qh

[font="Tahoma"]Who looks outside, dreams; who looks inside, awakes. – Carl Jung.[/font]

doug.baker-706021 SSC Journeyman Points: 90 More actions · Answer 15

Hi Gail,

First off, thanks for the great article and thanks to the SSC folks for making it so easy to find.

I've just started a new DBA job at a company and found some DBCC CHECKDB errors that fall into the second "non-serious" category you listed under Inaccurate Space Metadata.

In my case, the table with the issue happens to be a system table,

table 'sys.sysobjvalues' (object ID 60)

, and I wondered if that would impact (change) the answer you gave in any way.

Thanks,

Doug

DBCC output is below:

Msg 8914, Level 16, State 1, Line 1

Incorrect PFS free space information for page (1:75) in object ID 60, index ID 1, partition ID 281474980642816, alloc unit ID 71776119065149440 (type LOB data). Expected value 0_PCT_FULL, actual value 100_PCT_FULL.

Msg 8914, Level 16, State 1, Line 1

Incorrect PFS free space information for page (1:117) in object ID 60, index ID 1, partition ID 281474980642816, alloc unit ID 71776119065149440 (type LOB data). Expected value 0_PCT_FULL, actual value 100_PCT_FULL.

CHECKDB found 0 allocation errors and 2 consistency errors in table 'sys.sysobjvalues' (object ID 60).