Summary: SQL server error 823 occurs due to an operating system or an I/O error. It may impact database integrity and needs to be fixed immediately. This blog explains in detail about the error and how you can fix it. You may try using a SQL repair tool to fix consistency errors against the database that reported 823 error.
Contents
What is SQL error 823?
A SQL Server database encounters error 823 when an I/O operation (read or write request) fails. This usually happens due to issues with the operating system, drivers, or hardware issues. The SQL error 823 may also occur due to inconsistencies in the file system or a corrupted database file.
The complete text of the SQL 823 error message looks similar to:
|
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\ MSSQL\DATA\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. It is recommended to complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online |
|---|
In the error message, 823 is the error code. The severity level 24 means that the error occurred due to problems with the hardware or software. The state 2 is used to differentiate the error from other errors with the same number but in a different state.
Further, the error provides the following information:
- The data file (usually a .MDF file) against which the I/O (read or write) operation was performed.
- The offset, i.e., the physical byte offset from the start of the file, dividing it by 8192 will help you find the logical page number that is corrupted due to the error.
- Identifies whether a read or write request was made to a device.
What problems are associated with the 823 IO error?
This problem can be related to the following problems:
- A Torn Page
- Bad Page ID
- Insufficient bytes transferred
What is a torn page?
It is a page that was incorrectly written. We could say Torn Page Detection writes a bit for every 512 bytes on the page. It helps detect a page that is not successfully written to disk. But, it does not tell if the data stored to disk is actually correct as a couple of bytes may have been written incorrectly.
What is the error message for the torn page error?
The error will be like this one:
2015-08-05 16:51:18.90 spid17 Error: 823, Severity: 24, State: 2 |
|---|
How can I detect torn page IO error?
You can detect these errors when you run the CHECKDB command in your database.
What is Bad Page ID?
It is when the header page ID is not the same as the expended one when reading in the disk.
Why is the error insufficient bytes transferred produced in SQL Server?
It means that the API call was invoked successfully, but the bytes transferred are not the expected ones.
How to solve SQL Server error 823 ?
The first step would be to run a DBCC CHECKDB command and check if it returns consistency errors. If it does, try to restore your database from a backup. Once the database is restored, run a CHECKDB to verify that the problem is solved.
If the problem persists or backup is not available, try to repair the database using the following T-SQL statement:
DBCC CHECKDB (DB_NAME, REPAIR_REBUILD);To repair the database, you must restrict its usage to a single user. This requires changing the database state to Single-user mode. To do so, run the following T-SQL query:
ALTER DATABASE DB2 SET SINGLE_USER WITH ROLLBACK IMMEDIATE;You can also set the database to a Single-user using the point-in-click interface in SQL Server Management Studio (SSMS). For this, right-click on the database in SSMS and go to the Options page. On the ?Options? page, choose the SINGLE USER option from the Restrict Access drop-down box, then click OK.
After setting the database to Single_user mode, repair the database, and run ?DBCC CHECKDB? again to verify that the database was repaired.
Note: If the error persists, an issue with the hardware might have caused SQL error 823. In that case, you will need to repair your hard disk. Contact the IT members in charge of the hardware. Check also if there are fragmentation problems in the hard disk.
If you have a Torn page error, you can use the CHECKSUM and the torn page detection to repair and verify the errors.
What Else You Can Do to Resolve SQL Error 823?
Use Stellar Repair for MS SQL to fix database consistency errors reported by DBCC CHECKDB and preview the recoverable data. This SQL repair tool can be downloaded from the link below.
This software will repair the .MDF/.NDF files of SQL Server database. Before using the software, make sure to stop the SQL Server Service first. It is recommended to stop the service using the SQL Server Management Studio (SSMS) or the SQL Server Configuration Manager.
Now follow these steps to repair and recover your SQL database file:
- Browse or search the corrupt .MDF file.
- Once the .MDF file is uploaded, press the Repair button.
Note: If you have one or multiple secondary data files (.NDF file) associated with your database, then put all the NDF files at the location of the MDF file. Once everything is done, you can restart the SQL Server Service and rerun the CHECKDB command.
- Run standard scan to repair the selected database file quickly.
- Preview the repairable database objects and select all or specific objects you want to restore. Next, press the Save button from the File menu.
- Enter the details as shown in the following screen to complete the file saving process.
Once the software repairs the .MDF file, you will no longer see the SQL Server error 823.
Conclusion
This blog discussed the reasons that could result in SQL Server database error 823. It can be caused by hardware (disk errors, hardware or software failures) or system problems. Also, the blog explained how to fix the SQL 823 error by restoring the database from backup, running DBCC CHECKDB with a repair option, or using Stellar Repair for MS SQL software. Stellar Repair for MS SQL is a simple SQL repair tool. If you have any queries, please do not hesitate to write your questions and comments.
