dbcc repair_allow_data_loss

Hi newsgroup,
in the last days we had a lot of trouble with our SQL-Server2000 database.
We moved to complete new Hardware. Now it's solved (i hope so),
but we want to be prepared for the future. Therefore i've a few questions:
During the normal maintenance of the database some consistency errors were d
etected.
The REPAIR_ALLOW_DATA_LOSS option was suggested by dbcc checkdb.
We had the luck, that not data loss was mentioned in the log file.
So, can we be really sure that there was no data loss?
If there's a data loss, how detailed is the information about the lost data?
After the first run with the REPAIR_ALLOW_DATA_LOSS option the next checkdb
detected still errors. (The first job wrote to the log file that all errors
were corrected)
The check suggested the REPAIR_REBUILD option.
For me the question is, why are there still errors after the first run with
the
REPAIR_ALLOW_DATA_LOSS option? How can we avoid to run the job with a REPAIR
_XXXXX option more
than one time.
Thank you & Regards,
Sven KrampeHi Sven,
The reason it sometimes needs to be run twice or more is that some errors
can mask other errors. There are many different integrity checks performed
on the various structures in the database. If a problem is detected with a
higher level structure, this may prevent lower-level integrity checks from
running. Once the higher-level roblem is fixed, a subsequent check may
discover these masked errors.
We have made great strides in reducing this phenomenon in the upcoming Yukon
release.
As a side note, you should always endeavor to use your backups to recover
from corruption issues rather than running repair. The
repair_allow_data_loss option is aptly and deliberately named as it may need
to delete some of your data to remove corruption and does nothing to prevent
further hardware caused corruption (how could it?). You should assume that
data was lost if you had to use this option and so your business or
application logic may no longer be correct (constraints etc).
Let me know if you have any further questions.
Regards
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"skrampe" <anonymous@.discussions.microsoft.com> wrote in message
news:49F9BF8C-7B8B-4B10-BCB1-C60295BA60C8@.microsoft.com...
> Hi newsgroup,
> in the last days we had a lot of trouble with our SQL-Server2000 database.
> We moved to complete new Hardware. Now it's solved (i hope so),
> but we want to be prepared for the future. Therefore i've a few questions:
> During the normal maintenance of the database some consistency errors were
detected.
> The REPAIR_ALLOW_DATA_LOSS option was suggested by dbcc checkdb.
> We had the luck, that not data loss was mentioned in the log file.
> So, can we be really sure that there was no data loss?
> If there's a data loss, how detailed is the information about the lost
data?
> After the first run with the REPAIR_ALLOW_DATA_LOSS option the next
checkdb
> detected still errors. (The first job wrote to the log file that all
errors were corrected)
> The check suggested the REPAIR_REBUILD option.
> For me the question is, why are there still errors after the first run
with the
> REPAIR_ALLOW_DATA_LOSS option? How can we avoid to run the job with a
REPAIR_XXXXX option more
> than one time.
>
> Thank you & Regards,
> Sven Krampe|||...
You should assume that
data was lost if you had to use this option and so your business or
application logic may no longer be correct (constraints etc).
...
Just to be sure: You mean, that it's possible that data is lost and we do no
t have any information about this in the Log-File of the repair job?
We check our database every night.
So, what would you suggest if we'll find consistency errors again (with the
suggestion repair_allow_data_loss)?
Go back to the last complete backup? (lose 24 h work of the users)!
Have a system downtime and try to repair the database?
Thank you very much for your help!
Regards,
Sven Krampe|||If you do regular transaction log backups, you can do a log backup after you
discover the corruption, and then
restore latest clean db backup and all subsequent log backups. Most probably
, the corruption will not re-occur
when you restore the log backups and you'll have no data loss. Below are my
general recommendations, btw:
http://www.karaszi.com/sqlserver/in..._suspect_db.asp
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"skrampe" <anonymous@.discussions.microsoft.com> wrote in message
news:73BA7F69-450B-4CE9-8C7C-1D8DDB0F10AF@.microsoft.com...
> ...
> You should assume that
> data was lost if you had to use this option and so your business or
> application logic may no longer be correct (constraints etc).
> ...
> Just to be sure: You mean, that it's possible that data is lost and we do not have
any information about
this in the Log-File of the repair job?
> We check our database every night.
> So, what would you suggest if we'll find consistency errors again (with the sugges
tion
repair_allow_data_loss)?
> Go back to the last complete backup? (lose 24 h work of the users)!
> Have a system downtime and try to repair the database?
> Thank you very much for your help!
> Regards,
> Sven Krampe
>|||> Just to be sure: You mean, that it's possible that data is lost and we do
not have any information about this in the Log-File of the repair job?
I'm not sure what's captured in the log file but DBCC always provides
output.

> So, what would you suggest if we'll find consistency errors again (with
the suggestion repair_allow_data_loss)?
I would suggest you have a hardware issue if you see it again - in fact, I'd
suggest you do root-cause analysis to make sure you hardware is sound anyway
just because you saw this corruption.

> Go back to the last complete backup? (lose 24 h work of the users)!
> Have a system downtime and try to repair the database?
>
You should always be able to restore from your backups - repair should
always be a last resort. How often you take them is up to you - you must ask
yourself 'how much data can my business afford to lose?'. If the answer is
zero, then you need to beef up your backup strategy and put in place a
disaster recovery strategy (maybe use clustering, log shipping etc). I've
seen catastophic failures where the loss has only been 15 minutes or less
with a daily full backup and log backups every 15 mins.
Regards.
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"skrampe" <anonymous@.discussions.microsoft.com> wrote in message
news:73BA7F69-450B-4CE9-8C7C-1D8DDB0F10AF@.microsoft.com...
> ...
> You should assume that
> data was lost if you had to use this option and so your business or
> application logic may no longer be correct (constraints etc).
> ...
> Just to be sure: You mean, that it's possible that data is lost and we do
not have any information about this in the Log-File of the repair job?
> We check our database every night.
> So, what would you suggest if we'll find consistency errors again (with
the suggestion repair_allow_data_loss)?
> Go back to the last complete backup? (lose 24 h work of the users)!
> Have a system downtime and try to repair the database?
> Thank you very much for your help!
> Regards,
> Sven Krampe
>sql