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
Paul Randal
Paul Randal
SSCrazy Eights
SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)

Group: General Forum Members
Points: 8310 Visits: 1719
Yes, potentially.

You'd have to do the following:

- extract all inserts performed after the table was emptied
- restore the database to time just before the corruption occurred
- manually merge in the post-corruption inserts, taking care of duplicates etc

Tedious.

However, how would the corruption have removed all records in the table but still allow inserts? By 'corruption', do you mean 'someone accidentally deleted all the records'? That's the only way your scenario can occur IMHO.

Thanks

Paul Randal
CEO, SQLskills.com: Check out SQLskills online training!
Blog:www.SQLskills.com/blogs/paul Twitter: @PaulRandal
SQL MVP, Microsoft RD, Contributing Editor of TechNet Magazine
Author of DBCC CHECKDB/repair (and other Storage Engine) code of SQL Server 2005
michael merrill
michael merrill
Valued Member
Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)

Group: General Forum Members
Points: 60 Visits: 79
Thanks Paul. That is what I thought.

The idea that the table was actually corrupted (not by a user- but say a disk array error) and it still allowed inserts is the paranoid DBA in me talking! But just because you are paranoid, doesn’t mean... ;-)

I have just seen DBs get corrupted, and they function just “fine”- But most likely not this sort of scenario.

So you think that if a table had issues, SQL would not allow the inserts? If so, that is good. I would love a crash at that point instead of a silent failure.

What is bothering me is that SQL does not tell you right away that there is DB corruption. I run CheckDB once a day on all the production servers, but I would much rather know in 30 seconds!

What tool would you use to read the logs and merge the data back together in my hypothetical case?

Again Thank you! This site has saved our bacon more than once.

M
Paul Randal
Paul Randal
SSCrazy Eights
SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)

Group: General Forum Members
Points: 8310 Visits: 1719
You wouldn't read the logs. You'd have the corrupt database, and the restore pre-corruption database and then you'd manually merge the data in the two tables.

Paul Randal
CEO, SQLskills.com: Check out SQLskills online training!
Blog:www.SQLskills.com/blogs/paul Twitter: @PaulRandal
SQL MVP, Microsoft RD, Contributing Editor of TechNet Magazine
Author of DBCC CHECKDB/repair (and other Storage Engine) code of SQL Server 2005
GilaMonster
GilaMonster
SSC Guru
SSC Guru (228K reputation)SSC Guru (228K reputation)SSC Guru (228K reputation)SSC Guru (228K reputation)SSC Guru (228K reputation)SSC Guru (228K reputation)SSC Guru (228K reputation)SSC Guru (228K reputation)

Group: General Forum Members
Points: 228808 Visits: 46344
michael merrill (12/28/2012)
The idea that the table was actually corrupted (not by a user- but say a disk array error) and it still allowed inserts is the paranoid DBA in me talking! But just because you are paranoid, doesn’t mean... ;-)


Depending on how badly and what has been corrupted, the inserts may run fine or they may fail. If they run fine then the inserts will be logged normally (and hence present in log backups), if they fail, you get blatant error messages

What is bothering me is that SQL does not tell you right away that there is DB corruption.


Sure it does. The instant that any query encounters corruption you get an error in the error log (823 or 824 are the more common). However, if you don't read the corrupt page, SQL has no way of intuiting that a page on disk that it has not read has been damaged by the IO subsystem.

CheckDB, because it reads every page in the DB, will find all corrupt pages. Normal queries running against the DB might not use the pages that are damaged and hence will never notice that they're corrupt

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


Randall_M.
Randall_M.
Valued Member
Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)

Group: General Forum Members
Points: 62 Visits: 50
Thanks bunches Gail, I inherited a mess and one of your sections is going to help me recover a DB. :-D

Drive it like you stole it[size="4"][/size] w00t
Tim-153783
Tim-153783
SSC-Addicted
SSC-Addicted (406 reputation)SSC-Addicted (406 reputation)SSC-Addicted (406 reputation)SSC-Addicted (406 reputation)SSC-Addicted (406 reputation)SSC-Addicted (406 reputation)SSC-Addicted (406 reputation)SSC-Addicted (406 reputation)

Group: General Forum Members
Points: 406 Visits: 729
Make sure you check the database id when getting a corruption message. I sometimes see the corruption message in temdb - database 2.
Server: Msg 605, Level 21, State 3, Attempt to fetch logical page (1:39296) in database 2 failed. It belongs to allocation unit 72153958174228480 not to 5188243200689831936.
It turns out, a trace flag was somehow removed (or never added to the service start up) and the need to add back trace flag 4199 back to the server for 2008 R2 SP2 with the command dbcc traceon(4199, -1). This usually happens when someone forgets to put in it the service startup line.
raj k
raj k
Grasshopper
Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)

Group: General Forum Members
Points: 19 Visits: 296
Hi,
I have an "error" message in the sql error log, which after executing dbcc checktable on a table tells me that it "found 1 errors and repaired 0 errors." The dbcc checktable itself ran to completion on the table. There is nothing more in the sql error logs to indicate any corruption. There is nothing in the windows system/application logs either. Dbcchecktable was done with no_infomsgs option. Wondering if this means there is a possible corruption issue with the table? I would really appreciate suggestions. This problem occurred in Production environment and as you can guess, am very keen to get some suggestions
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