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


Help, my database is corrupt. Now what?


Help, my database is corrupt. Now what?

Author
Message
raj k
raj k
Forum Newbie
Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)

Group: General Forum Members
Points: 7 Visits: 296
thank you for the link posted
GilaMonster
GilaMonster
SSC Guru
SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)

Group: General Forum Members
Points: 87661 Visits: 45272
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
kellyj481
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
Points: 3 Visits: 0
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
quackhandle1975
Hall of Fame
Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)

Group: General Forum Members
Points: 3243 Visits: 1240
Added to the briefcase, nice insurance policy when the worst happens. w00t

qh

Who looks outside, dreams; who looks inside, awakes. – Carl Jung.
doug.baker-706021
doug.baker-706021
SSC Rookie
SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)

Group: General Forum Members
Points: 28 Visits: 81
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).
GilaMonster
GilaMonster
SSC Guru
SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)

Group: General Forum Members
Points: 87661 Visits: 45272
Could you start a new thread for your corruption problem please?

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


doug.baker-706021
doug.baker-706021
SSC Rookie
SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)

Group: General Forum Members
Points: 28 Visits: 81
Sorry - I probably asked my question in the wrong way.

What I really wanted to know was whether you would proceed differently (i.e., more cautiously) knowing the corruption affected a system table as opposed to a user table. Or is a table is a table is a table?

I wasn't really looking for a solution to my specific corruption problem. I've already written and tested a fix script (thanks to advice in your article and from Paul Randal's on SQLSkills.com).

I do see now that a lot of people have posted specific issues in this forum, and I will be careful to avoid that!

Thanks,
Doug
GilaMonster
GilaMonster
SSC Guru
SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)

Group: General Forum Members
Points: 87661 Visits: 45272
doug.baker-706021 (12/11/2012)
What I really wanted to know was whether you would proceed differently (i.e., more cautiously) knowing the corruption affected a system table as opposed to a user table.


Generally, yes. A system table cannot be repaired, cannot have indexes rebuilt. Typically corruption in a system table means restore from backup with few to no other options available.

Since the error you have isn't really corruption (just bad metadata), perhaps it's fixable. Not an error I've encountered in a system table before.

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


doug.baker-706021
doug.baker-706021
SSC Rookie
SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)

Group: General Forum Members
Points: 28 Visits: 81
Thank you, Gail. I appreciate the additional information.

Fixing this particular system table in this particular case appears to have had no adverse affects in the test environment, so I think your comment about it not really being corruption hits the point here. I think now I will go get my copy of SQL Server 2008 Internals and read the DBCC chapter by Paul Randal you mentioned in the footnote of your article, and ponder this further.

--Doug
michael merrill
michael merrill
Grasshopper
Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)

Group: General Forum Members
Points: 20 Visits: 79
I had a disk array go nuts recently and it corrupted MSDB and a an application DB. I could repair MSDB, and we had Full and Log backups for the application DB. Whew! We survived.

BUT! I have a question... I’ll do this in an outline so maybe it will be easier to follow:

1. Let's say that you have a application DB that gets fully backed up at 1:00am
2. It has transaction log backups every 15 minutes
3. The DB gets corrupted at, say, 6pm.
4. The corruption results in the loss of all the records in a user table
5. At 6:30pm, the application actually pulls off inserting records into the table
6. Your checkdb does not run until 8:00pm.

Is there a chance that the transaction log backup will be funky because of inserts (etc) into the corrupted table? To be specific, if there are no records in the table because of the corruption, that could result in inserts that would not have normally happened if there were records in the table. So, when you apply the log backup, it would try to insert duplicate records?

I am sure that I am ignorant of something (like something in the nature of log backups), but I have to ask!

Thanks,
M
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