resolve sql error 823

MS SQL Server uses MDF file to store your crucial data. You often need to execute queries to add, delete, or modify data stored in the MDF file. Sometimes, while querying the MDF database you may get various errors on your computer screen. Interpreting some of the error messages is quite difficult because there is no description added with the error message. You just see a number indicating that there is some sort of error. One of such error number is “Error 823.” This error often occurs due to disk read errors or database file corruptions. You may finally need to repair and rebuild the sql database in order to gain access to the data contained in the MDF file.

This article describes what that error number is, why it occurs, and how you can resolve it. Just by seeing the error number, you cannot predict its reason until you do not dig into the error log. Similar is the case with Error 823. When you view the error log to find out the reason behind the error, you see following information logged into the error log file:

“2010-06-10 12:11:29.55 spid58 Error: 823, Severity: 24, State: 2.
2010-06-10 12:11:29.55 spid58 The operating system returned error 38(Reached the end of the file.) to SQL Server during a read at offset 0x000000a72c0000 in file ‘C:\Program Files\Microsoft SQL Server\MSSQL10.SQL2008\MSSQL\DATA\mysqldatabase.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. This error often occurs due to error in database pages (including page id error). Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors.”

Cause for the Error Message

The aforementioned error message occurs when there is some sort of inconsistency in the database or the sql database file (.MDF file) is damaged.

Solution

You can get rid of the Error 823 by considering one of the following two methods:

• Bring the database back into the consistent state: This is the first step that you should consider after getting the Error 823. To bring the database back into the consistent state, use chkdsk utility(please note that chkdsk is highly destructive utility and it may result in a complete data loss situation). This utility helps you check and resolve the system’s inconsistency related issues and bring the database back in consistent state.
• Repair the Database: If the above method gets failed to resolve the Error 823 then it means the SQL database is damaged. Therefore, you need to repair it.

You can repair it using SQL repair and recovery tool. A good quality MDF repair tool enables you to recover the data stored in the damaged MDF file within few minutes. The Repair SQL database tool supports MS SQL Server 2000, 2005, and 2008.