Sunday, February 19, 2012

DBCC CheckDB Repair Allow Data Loss

I have a farily large db (~450 gig) for a customer. After rebooting the
server last week, the DB came up suspect. Looking in the logs, there were
several 823 errors. Our 3rd party hardware hosting company says there are
no disk problems.
We have no back up of the DB (yea, keep your comments to this to yourself).
Running DBCC CheckDB, 2 tables were identied as having a problem. This took
58 hours to run.
Recomendation was to run dbcc checkDB repair_allow_data_loss. I'm ok with
doing this as the DB is a reporting DW database, and can be rebuilt with any
missing data. It's been running 48 hours now. Will it take the same
amount of time as the checkDB (58 hours)? Longer? Any chance after this
runs that it will not be able to repair and the DB will be not usable.
Anyone with experience with this please let me know.
Thanks.Lots of questions/comments to ask you here.
1) What is the output of the CHECKDB? Why not just rebuild the database as
you described below? Did you look through the errors do determine whether
data loss is to be expected?
2) how long does CHECKDB normally take to run on this database? 58 hours is
excessively long (unless certain errors have been found and have kicked off
other, deeper checks)
3) what's the h/w of the box (# of CPUs, IO bandwidth of IO subsystem)
4) how does the hosting company know there are no disk problems? 823s are
hard IO errors (i.e. the page on disk is bad in some way)
5) no way to tell whether any of the errors can't be repaired (there are
some documented unrepairable errors) without seeing the list...
6) repair may take longer - depends what it has to do. It usually takes the
same amount of time as the CHECKDB plus any time required to effect the
repairs.
Thanks
Paul Randal
Lead Program Manager, Microsoft SQL Server Storage Engine
http://blogs.msdn.com/sqlserverstor...ne/default.aspx
This posting is provided "AS IS" with no warranties, and confers no rights.
"Steve" <ss@.Mailinator.com> wrote in message
news:uZK3jAPpGHA.220@.TK2MSFTNGP05.phx.gbl...
>I have a farily large db (~450 gig) for a customer. After rebooting the
>server last week, the DB came up suspect. Looking in the logs, there were
>several 823 errors. Our 3rd party hardware hosting company says there are
>no disk problems.
> We have no back up of the DB (yea, keep your comments to this to
> yourself).
> Running DBCC CheckDB, 2 tables were identied as having a problem. This
> took 58 hours to run.
> Recomendation was to run dbcc checkDB repair_allow_data_loss. I'm ok with
> doing this as the DB is a reporting DW database, and can be rebuilt with
> any missing data. It's been running 48 hours now. Will it take the same
> amount of time as the checkDB (58 hours)? Longer? Any chance after this
> runs that it will not be able to repair and the DB will be not usable.
> Anyone with experience with this please let me know.
> Thanks.
>

No comments:

Post a Comment