Does Repair_Allow_Data_Loss cause data loss?

Short answer: Yes. That’s why the option has that name.

Long answer:

When CheckDB finds corruption it will, as part of the output, include the minimum level of repair that will fix the errors that it found. In SQL 2005 and higher, it will look something like the following.

CHECKDB found 42 allocation errors and 137 consistency errors in database ‘SomeDB’.
repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB (SomeDB).

There are two options for the minimum repair level. REPAIR_REBUILD and REPAIR_ALLOW_DATA_LOSS.

If the minimum level is REPAIR_REBUILD then it means that the corruption was confined to just the nonclustered indexes. This is good. Nonclustered indexes are redundant data and hence can be dropped and recreated (which is pretty much what CheckDB with the REPAIR_REBUILD option does)

If the minumum level is REPAIR_ALLOW_DATA_LOSS then running CheckDB with the REPAIR_ALLOW_DATA_LOSS option will cause data loss. That’s why the option is named like that. How much it will lose depends on the extent of the corruption.

There’s one other case. What about when checkDB doesn’t specify a minimum level?

CHECKDB found 3 allocation errors and 7 consistency errors in database ‘SomeDB’.

That means that there’s damage to one or more of the allocation pages or there’s corruption in the system tables. That kind of corruption isn’t repairable, so no minimum repair level can be specified.

So, in conclusion, will running CheckDB with the REPAIR_ALLOW_DATA_LOSS option cause data loss? If that’s the minimum level that CheckDB specified, then yes it will.

For more info, see Paul Randal’s blog and an article that I wrote at SQL Server Central.

Edit: There is one case where the repair level repair_allow_data_loss is required, but no data will be lost. This is the case of the incorrect PFS page (see my article at SQLServerCentral for more info). If that is the only error, repair_allow_data_loss will be the minimum level, but no data will actually be lost.

6 Comments

  1. Raj

    Hi Gail,
    My comment is not related to this post. But I badly wanted to seek your advice.So, I am using this page to post my question.

    Moderator: Long, unrelated question removed.

    Thanks in Advance,
    S.V.Nagaraj

    Reply
  2. Gail

    Can you maybe post this on a SQL-related forum? SQLServerCentral, SQLTeam or Microsoft’s MSDN forums?

    Reply
  3. RBarryYoung

    OK Gail, let me see if I’ve got this right. You’re saying that REPAIR_ALLOW_DATA_LOSS, actually Allows Data Loss?!? How could anyone possibly be expected to know that?

    ๐Ÿ™‚

    Reply
  4. Gail

    I know Barry, it’s a difficult one. I mean, it’s not as if the name gives us any clues. ๐Ÿ˜‰

    I wrote that in frustration after someone asked exactly that question. “Will REPAIR_ALLOW_DATA_LOSS cause Data Loss?”

    Reply
  5. Raj

    Hi Gail,
    Thx for the reply. I have already posted on SQLServerCentral with no luck. Will try other options. I used your forum only becos i couldnt find your email Id. Anyways, If you happen find any info please put a post on it as it will help many like me ๐Ÿ™‚

    Regards,
    S.V.Nagaraj

    Reply
  6. Pingback: Restaurando um Database Suspect – SQL Server | OnlyWhatMatters

Leave a Comment

Your email address will not be published. Required fields are marked *