5

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!

  • Run DBCC CHECKCATALOG (Your DBName) WITH NO_INFOMSGS; and post its output. What is the compatibility mode of DB? – SQLPRODDBA May 10 '16 at 5:29
  • 4
    That is the RTM build of 2012 which is known to have data corruption issues. You should have and should later update to the current SP and CU; which is up to SP3 CU2. 1. Can you rerun Dbcc Checkdb('...') With ALL_ERRORMSGS; does it provide any further output? 2. Can you check sys.databases to see if is_auto_close_on = 0 on this database (if enabled it can cause spurious dbcc severe errors). 3. Are you able to restart the instance and confirm dbcc still fails (a failed mirroring session can also cause spurious dbcc severe errors). – Cody Konior May 10 '16 at 8:00
  • Did you check event viewer for errors? A problem with storage or hardware or drivers can raise this kinds of errors: support.microsoft.com/en-us/kb/2015755 – user_0 May 10 '16 at 8:37
  • 4
    Since CHECKDB is not completing I suggest you restore the DB from good backup. – SQLPRODDBA May 10 '16 at 9:29
1

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.

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.