Which part of 'REPAIR_ALLOW_DATA_LOSS' isn't clear?
In my chalk/talk at TechEd yesterday, I asked the crowd "what's the purpose of repair?" One person got it right. The purpose is not to save data. Surprised? The purpose is to get the database back to a structurally and transactionally consistent state so that processing can safely continue.
We chose the name of this repair option very carefully and it's pretty obvious what you're implying when you use it - "please fix up my database and if you have to delete some data to do that then - ok".
When CHECKDB reports that the recommended repair option is REPAIR_ALLOW_DATA_LOSS, that's because it's going to have to delete something to repair the damaged database. We're not talking about damaged non-clustered indexes here, we're talking about heap or clustered index data records or pages being deleted.
For instance, if a data record has a text column, but we can't find the matching off-row text storage, then the record is deleted because it doesn't contain a valid table row. If that row represents a customer account (for instance a bank account as described in the previous post), you've got one annoyed customer on your hands. However, if the data record has a corrupt structure, such that the variable-length offset array pointer is pointing off beyond the end of the record, then we can't trust anything on the page and we have to delete the entire thing. Depending on your fanout, that could mean deleting 300 data records or more! That means you'll be dealing with more than 300 annoyed customers, who may tell their friends, and so on - pretty soon your bank is bankrupt and you're on the street destitute! Well, maybe I got a bit carried away there but you get the idea - data loss is not good for your business.
But wait, some may say, surely there's some good that can come of running repair? Actually, no - there's just more badness. Repair is just trying to get the physical structures fixed up - it pays no attention to higher-level logical structures. This means that, for instance, foreign-key constraints may be broken along with any inherent business logic in your database.
So, given all the nasty things that can happen when you run repair - make it your absolute last choice, not your first choice - always have a good backup strategy.
Next time - how we took the worst two things you can do to your database (rebuilding the transaction log and running REPAIR_ALLOW_DATA_LOSS) and made them into a cool new feature in SQL Server 2005.
(Btw, if you're ever in Boston, the sushi bar in the Boston Mariott Copley Place does fantastic sashimi, and Sam Adams is almost as good as Mac & Jacks - guess what I had for dinner tonight...)
Comments
Anonymous
June 16, 2006
Ahhhh I always though allow_data_loss always meant that no data loss occurs, but actually a full backup automatically happens and the data is magically repaired to it's pristine state with no manual intervention. I think I read that on a website somehwere or something. It had something about the best way to delete some extra file that wasn't needed... I think it was an LDF or something?? ;^) seriously though, keep up the good posts. hopefully some of these entries will get linked enough to land on google above the "dangerous advice with no warning" websites out there.Anonymous
June 18, 2006
Emergency mode repair? In a couple of previous posts I explained how the two worst things you could do...Anonymous
February 07, 2007
I was teaching at a Microsoft-internal class last week and there was a discussion on what corruptionsAnonymous
May 31, 2009
PingBack from http://outdoorceilingfansite.info/story.php?id=4453Anonymous
June 13, 2009
PingBack from http://outdoordecoration.info/story.php?id=4089Anonymous
June 17, 2009
PingBack from http://patiosetsite.info/story.php?id=541Anonymous
June 18, 2009
PingBack from http://homelightingconcept.info/story.php?id=2015Anonymous
June 18, 2009
PingBack from http://patiocushionsource.info/story.php?id=1362Anonymous
June 27, 2012
The comment has been removed