Sql-server – Getting error running DBCC Checktable(tablename, repair_allow_data_loss)

dbcc-checkdbsql serversql server 2014

I ran DBCC CheckDB and got some consistency errors in the database. In an attempt to recover all the data I have exported out all the data from the database to a new database except for one table. When I try to copy all the data from that table to the new database I get an error that says

"A severe error occurred on the current command. The results, if any,
should be discarded."

So I ran DBCC CheckTable(tablename) on the table to try to ensure that this was the table that was causing the issue. It gave the same consistency errors that were coming from the CheckDB.

Once I ran CheckTable it told me the minimum I could do is run with repair_allow_data_loss. I set the database into SINGLE_USER mode and then I ran the DBCC CheckTable(tablename, repair_allow_data_loss) command and now I get this error

Repair statement not processed. One or more files in the database are
read-only and must be made writeable in order to run repair.

I checked to make sure the files and the file groups are all writeable and they are, so I'm not sure what is causing the issue.

I've tried taking a backup and restoring that to a different server, I've tried making a new copy of the database, I've tried making the files read-only and then making them writeable again. None of those things worked and all produced the same error about the files being readonly.

Anybody have any thoughts on what else I might try?

For reference, I am running SQL Server 2014 SP2.

EDIT: Here is there output using WITH NO_INFOMSGS, ALL_ERRORMSGS

  • Msg 8952, Level 16, State 1, Line 1
    Table error: table 'sys.sysschobjs' (ID 34). Index row in index 'nc1' (ID 2) does not match any data row. Possible extra or invalid keys for:
  • Msg 8956, Level 16, State 1, Line 1
    Index row (1:18806:22) with values (nsclass = 0 and nsid = 1 and name = 'tmp324504_734167628_1' and id = 385811436) pointing to the data row identified by (id = 385811436).
  • CHECKDB found 0 allocation errors and 1 consistency errors in table 'sys.sysschobjs' (object ID 34).
  • Msg 8944, Level 16, State 12, Line 1
    Table error: Object ID 1993058136, index ID 1, partition ID 72057684034650112, alloc unit ID 71906736119218176 (type LOB data), page (1:28439), row 0. Test (ColumnOffsets <= (nextRec – pRec)) failed. Values are 32512 and 8037.
  • Msg 8944, Level 16, State 12, Line 1
    Table error: Object ID 1993058136, index ID 1, partition ID 72057684034650112, alloc unit ID 71906736119218176 (type LOB data), page (1:28439), row 0. Test (ColumnOffsets <= (nextRec – pRec)) failed. Values are 32512 and 8037.
  • Msg 8965, Level 16, State 1, Line 1
    Table error: Object ID 1993058136, index ID 1, partition ID 72057684034650112, alloc unit ID 71906736119218176 (type LOB data). The off-row data node at page (1:28439), slot 0, text ID 69999198208 is referenced by page (1:27785), slot 0, but was not seen in the scan.
  • Msg 8928, Level 16, State 1, Line 1
    Object ID 1993058136, index ID 1, partition ID 72057684034650112, alloc unit ID 71906736119218176 (type LOB data): Page (1:28439) could not be processed. See other errors for details.
  • Msg 8929, Level 16, State 1, Line 1
    Object ID 1993058136, index ID 1, partition ID 72057684034650112, alloc unit ID 72057693155753984 (type In-row data): Errors found in off-row data with ID 69999198208 owned by data record identified by RID = (1:869011:3)
  • CHECKDB found 0 allocation errors and 6 consistency errors in database 'DataCorruption'.
    repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB (DataCorruption).

Best Answer

From the comments I see that you have it narrowed down to a single corrupt row. You may be able to get the underlying data (or most of the data at least) in that row via a hex editor. Paul Randal has good information on how to crack open MDF files this way and here is an article by Kendra Little as well.

https://littlekendra.com/2011/01/24/corrupthexeditor/

The article is on how to corrupt a database with a hex editor but you've already got that so look at the steps right below "It’s the Moment We’ve Been Waiting For: Trash That Page".

Extracting information in this way can't be healthy for the database, so ensure you are using a backup!

Depending on the data, you can usually see enough to piece together the corrupt values.