Prev: which transaction log was applied last?
Next: unable to schedule file copy within sql server sched job
From: xo on 9 Jul 2010 21:45
Hi,

URGENT! I got the following errors from the DBCC checkdb result on the
production system which indicates there are corruptions on some
indexes and tables. Can someone provide some steps and methods to
correct the problem without data loss. I cannot afford to lose any
data or use repair_allow_data_loss to fix it. And the problem exists
three nights, so backups are no good.

The DBCC result -
Server: Msg 8952, Level 16, State 1, Line 1
Table error: Database 'ABC', index 'A.te_mast.i_te_mast4' (ID
197627797) (index ID 5). Extra or invalid key for the keys:
Server: Msg 8956, Level 16, State 1, Line 1
Index row (1:239548:22) with values (cur_app_level = 0 and ppend =
Jul 9 2010 12:00AM and employee = '1111111111') points to the data
row identified by (RID = (1:375367:9)).
Server: Msg 8909, Level 16, State 1, Line 1
Table error: Object ID 197627797, index ID 5, page ID (1:239556). The
PageId in the page header = (1:239548).
There are 5597900 rows in 91769 pages for object 'A.te_mast'.
CHECKDB found 0 allocation errors and 2 consistency errors in table
'A.te_mast' (object ID 197627797).

Server: Msg 8928, Level 16, State 1, Line 1
Object ID 764581812, index ID 0: Page (1:239556) could not be
processed. See other errors for details.

DBCC results for 'A.payout'.
There are 48991226 rows in 1816936 pages for object 'A.payout'.
CHECKDB found 0 allocation errors and 1 consistency errors in table
'A.payout' (object ID 764581812).

Server: Msg 8909, Level 16, State 1, Line 1
Table error: Object ID 1232059475, index ID 0, page ID (1:1295231).
The PageId in the page header = (1:1295223).

DBCC results for 'A.online_sched_ent'.
There are 13129 rows in 338 pages for object 'A.online_sched_ent'.
CHECKDB found 0 allocation errors and 1 consistency errors in table
'A.online_sched_ent' (object ID 1232059475).

Server: Msg 8928, Level 16, State 1, Line 1
Object ID 1740585289, index ID 0: Page (1:1295230) could not be
processed. See other errors for details.
Server: Msg 8939, Level 16, State 1, Line 1
Table error: Object ID 1740585289, index ID 0, page (1:1295230). Test
(IS_ON (BUF_IOERR, bp->bstat) && bp->berrcode) failed. Values are
2057 and -1.
Server: Msg 8928, Level 16, State 1, Line 1
Object ID 1740585289, index ID 0: Page (1:1295231) could not be
processed. See other errors for details.

DBCC results for 'A.sched'.
There are 23173828 rows in 731141 pages for object 'A.sched'.
CHECKDB found 0 allocation errors and 3 consistency errors in table
'A.sched' (object ID 1740585289).

CHECKDB found 0 allocation errors and 7 consistency errors in database
'ABC'.
repair_allow_data_loss is the minimum repair level for the errors
found by DBCC CHECKDB (ABC ).

Thanks in advance.

From: Erland Sommarskog on 10 Jul 2010 05:41
[posted and mailed]

xo (xo5555ox(a)gmail.com) writes:
> URGENT! I got the following errors from the DBCC checkdb result on the
> production system which indicates there are corruptions on some
> indexes and tables. Can someone provide some steps and methods to
> correct the problem without data loss. I cannot afford to lose any
> data or use repair_allow_data_loss to fix it. And the problem exists
> three nights, so backups are no good.

In case you have not sensed it: you are in deep sh*t, sorry.

The first thing you should do is to find new hardware for this database,
because this corruption is most certainly due to some hardware-related
problem. (Bad driver, bad disk sectors stc.)

On the new server restore a couple of copies of the database. Two
instances that are copy its current shape, and a third which is the
most recent clean backup. Maybe you also want the intermediate backups
as well.

On the database that is to be the production database, run DBCC CHECKDB
with REPAIR_ALLOW_DATA_LOSS. Then try to use the copies to fill in the
bits and pieces that are missing.

You should also run consistency checks on application level in the
database, and run DBCC CHECKCONSTRAINTS and DBCC CHECKCATALOG. Most likely
you have data corruption that does not manifest itself in errors that
DBCC CHECKDB finds. For instance, if a broken bit changes the id of 4
to 1028, and both are valid id, there is no DBCC that will detect this.

I will also give the advice that trying to resolve this only through
newsgroups assistance is not going to work. I assume that you have a
requirement to have this database up and running on Monday morning the
latest, and that will be a very tough game. Hopefully, you can have
the database accessible on new hardware then, and you can sort out the
data corruption later.

If you feel uncertain, open a case with Microsoft, but you will have
to pay through the nose. Or try to find consultants locally that are
willing to help you. (You will still have to pay a lot.)

Again: the most important for you is to find new hardware. It's useless
to continue with what you have now.

Finally: I've mailed you a courtsey copy, since this is apparently an
urgent issue. But beware that it's useless to respond by mail. I'm about
to leave home to enjoy a wonderful summer day here in Stockholm, and
there is a football game on the telly tonight. You need someone onsite,
or at least by phone to help you. And, no, I don't envy you.

--
Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

 | 
Pages: 1
Prev: which transaction log was applied last?
Next: unable to schedule file copy within sql server sched job