Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««56789

Help, my database is corrupt. Now what? Expand / Collapse
Author
Message
Posted Friday, December 28, 2012 4:23 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, July 3, 2014 4:00 PM
Points: 2,038, Visits: 1,663
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
Post #1401092
Posted Friday, December 28, 2012 4:49 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, June 12, 2014 3:07 PM
Points: 4, Visits: 78
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
Post #1401093
Posted Friday, December 28, 2012 4:57 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, July 3, 2014 4:00 PM
Points: 2,038, Visits: 1,663
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
Post #1401095
Posted Saturday, December 29, 2012 2:20 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 5:18 PM
Points: 42,437, Visits: 35,492
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 2008, MVP
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

Post #1401150
Posted Monday, September 23, 2013 1:47 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, December 18, 2013 9:25 PM
Points: 10, Visits: 50
Thanks bunches Gail, I inherited a mess and one of your sections is going to help me recover a DB.


Drive it like you stole it[size="4"][/size]
Post #1497530
« Prev Topic | Next Topic »

Add to briefcase «««56789

Permissions Expand / Collapse