0

I am getting a lot of errors from DBCC CHECKDB, including:

Msg 8939, Level 16, State 98, Line 1
Table error: Object ID 0, index ID -1, partition ID 0, alloc unit ID 3667181342891245568 (type Unknown), page (7791:-1694668604). Test (IS_OFF (BUF_IOERR, pBUF->bstat)) failed. Values are 133129 and -12.

Msg 8928, Level 16, State 1, Line 1
Object ID 405576483, index ID 73, partition ID 72057594049200128, alloc unit ID 72057594054246400 (type In-row data): Page (1:194923) could not be processed. See other errors for details.

Msg 8976, Level 16, State 1, Line 1
Table error: Object ID 405576483, index ID 73, partition ID 72057594049200128, alloc unit ID 72057594054246400 (type In-row data). Page (1:194923) was not seen in the scan although its parent (1:186194) and previous (1:194922) refer to it. Check any previous errors.

Msg 8980, Level 16, State 1, Line 1
Table error: Object ID 405576483, index ID 73, partition ID 72057594049200128, alloc unit ID 72057594054246400 (type In-row data). Index node page (1:186194), slot 103 refers to child page (1:194924) and previous child (1:194923), but they were not encountered.

Msg 8978, Level 16, State 1, Line 1
Table error: Object ID 405576483, index ID 73, partition ID 72057594049200128, alloc unit ID 72057594054246400 (type In-row data). Page (1:194932) is missing a reference from previous page (1:194931). Possible chain linkage problem.

Most importantly, at the end, the following is relayed:

CHECKDB found 0 allocation errors and 28 consistency errors in database 'houseme'.

repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB (houseme).

What do I do?

  • added last part, is that what ur looking for? database backups are not an option..(dont ask why) :D – Zoinky Feb 18 '17 at 15:22
  • 5
    Of course they aren't. Most people don't care about backups until they need them and it's too late. This is like getting in a car accident, going to the hospital, and then trying to put on your seatbelt. – Aaron Bertrand Feb 18 '17 at 15:43
7

The output of DBCC CHECKDB has told you exactly what to do:

USE [master];
GO

ALTER DATABASE houseme SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
DBCC CHECKDB(N'houseme', REPAIR_ALLOW_DATA_LOSS);
ALTER DATABASE houseme SET MULTI_USER;

In case the name of the option isn't obvious enough, this can lead to data loss - but without backups, you don't really have any other choice.

If this doesn't work, and you don't have backups, well, you may have just learned a very expensive lesson about why you should have backups. You can see if Paul Randal has some other ideas, or this idea from Steve Stedman. No guarantees, of course.

If you still have consistency errors after multiple tries, it might be time to create a new database, migrate as much consistent data as you can, and cut your losses.

  • There are options for repair without data loss, when no backups exist. Even if you must use REPAIR_ALLOW_DATA_LOSS there are steps you can take before hand to limit that loss Steve Stedman has some ideas – James Jenkins Jan 4 at 18:10
  • @James Do you have a more specific link? That's just a set of 217 search results on his site for checkdb – Aaron Bertrand Jan 4 at 18:14
  • I was following your example in the Paul Randal link :) here is an example. Brent Ozar solved it with no backups and no data loss P.S. This comes from the 2017 PASS presentation, which I am just now watching and found this SE post while problem solving. – James Jenkins Jan 4 at 18:32
  • Okay, thanks... there is a difference, of course, but I'm not going to bother. – Aaron Bertrand Jan 4 at 18:38

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.