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...)