Sql-server – DBCC CHECKDB errors

corruptiondbcc-checkdbsql server

I am having some issues with my SQL Server database. When I run this:

select  * from dbo.Entity
where Oid='191FAF30-4729-4145-8106-60E34A8E164C'

…it spits out the following error.

Msg 823, Level 24, State 2, Line 2
The operating system returned error 1(Incorrect function.) to SQL Server during a read at offset 0x00000021442000 in file 'D:\Database\db.mdf'.
Additional messages in the SQL Server error log and system event log may provide more detail. This is a severe system-level error condition that threatens database integrity and must be corrected immediately.
Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online.

The event log error is same as above.

So I ran a DBCC CHECKDB and it spit out the following errors:

CHECKDB found 0 allocation errors and 0 consistency errors in database
A severe error occurred on the current command. The results, if any, should be discarded.

I tried

dbcc checktable ('Entity')

but message was:

Msg 0, Level 11, State 0, Line 0
A severe error occurred on the current command.
The results, if any, should be discarded.

Version information:

Microsoft SQL Server 2012 - 11.0.2100.60 (X64) 
Feb 10 2012 19:39:15 
Copyright (c) Microsoft Corporation
Enterprise Edition: Core-based Licensing (64-bit) on Windows NT 6.2 <X64> (Build 9200: )

Any suggestions on how to repair the corrupt table would be greatly appreciated!

Best Answer

When a Operating System error is encountered during the IO request, this will be reported as a 823 error. 823 error messages are reported only for failed OS IO operation errors. Given that, you need to check your system event log for any disk or file system related entries. You will need to fix the disk or file system problems or else the corruption will return.

Msg 0, Level 11, State 0, Line 0
A severe error occurred on the current command.
The results, if any, should be discarded.

This error is generally thrown when we have corrupt or inconsistent metadata.

• The actual error would be: Database %d has inconsistent metadata. This error cannot be repaired and prevents further DBCC processing. Please restore from a backup

• The metadata inconsistency error is in most cases irreparable Before any attempts to fix the corruption or recover the data, copy the mdf and ldf files off to a safe location. That way you can at least always return to your current state should your recovery attempts further damage the database.

You have a few options to try: 1. Restore from backup. 2. Manually extract as much data as you can, create a new table, import the data into the new table, delete corrupted table, rename new table to corrupted table name. 3. Run DBCC CHECKDB REPAIR_ALLOW_DATA_LOSS. <--this probably won't work 4. Purchase expensive 3rd party application to recover the data.

Only the restore from backup is guaranteed to work, as long as you haven't been backing up a corrupt database.

Related Question