Sql-server – How to fix errors reported by DBCC CHECKDB

dbcc-checkdbsql server

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?

Best Answer

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.

Related Question