Getting error running DBCC Checktable(tablename, repair_allow_data_loss)

Question

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).

Answer 1

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.

Answer 2

First, determine how valuable that one last row you can't get is. Chances are, it's really not that important, so you can stop, since you got every other row out of the table.

If it is that important, keep working, but understand that corrupt data without a good pre-corruption backup means data loss!!!

If it's complaining about files and/or filegroups being read only, and you've checked files and filegroups within SQL and even changed them back and forth... did the changes work on every file and every filegroup? Try again; if you tried to set one READ_ONLY and it failed, that's a clue. Also look at the OS filesystem level - is the actual MDF or NDF read only? Does SQL Server have FULL CONTROL of the directory and the file both?

Perhaps it's financial data, and can be recovered by a reconciliation process, since the numbers all have to balance.

Perhaps that row, or most of it, can otherwise be re-created from the rest of the data, knowing the business rules for that data?

Was that table part of any exports, output files, reports, extracts, etc. that are still available? If so, you may be able to find some or all of that row from those output sources.

You can certainly use a hex editor and, if your data isn't encrypted, try to find the correct 8k page of that particular table, then look carefully to try and find the affected row's data; see what you can find, but this is at best guesswork, and SHOULD NOT be relied on - you're looking at data that is either actually corrupt... or that's not the correct data in the first place.