Tuesday, July 19, 2011

How to Fix “Error: 823” in Microsoft SQL Server

Are you getting system-level errors while accessing your Microsoft SQL Server database? Are you unable to perform any I/O (Input/Output) operation on your database? Well, the problem generally occurs due to either conflicts of Windows API used by SQL Server or SQL Server database corruption. It leaves your database in an unusable state and leads to severe data loss situations. In order to get your mission critical data back in such cases, you must go for SQL database recovery solutions.

For instance, you may run across an error message similar to the following one in Windows Application Event Log or Microsoft SQL Server ERRORLOG:

"2010-03-06 22:41:19.55 spid58 Error: 823, Severity: 24, State: 2.
2010-03-06 22:41:19.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\my_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."

When a program encounters the above error message while modifying or querying the data, error is returned to that program and SQL Server database connected is closed.

Root of the problem:

The problem is caused by Windows APIs, used by Microsoft SQL Server. These APIs (such as WriteFile, ReadFile, WriteFileGather, and ReadFileScatter) are used to perform Input/Output operations. Upon completion of I/O operations, MS SQL Server examines for errors associated with API calls. When API calls betrays with OS error, you come across this behavior of SQL Server. The problem may also occur due to severe corruption to Microsoft SQL Server database.


Go through the below steps to recover SQL server database from corruption:
Review suspect pages table.

Check database consistency.

Review Windows Application Event Log to find operating system and file system related errors.

Restore database from current backup.

If the above methods fail to perform SQL Server recovery, you are required to opt for advanced third-party applications.

No comments:

Post a Comment