Summary: Sometimes we receive the SQL server error 823 and when this error occurs, It is hard to estimate the level of severity, if it persist what problems it may cause and what is the solution. In this article, we will show causes and how to solve the problem of the SQL Server error number 823.
What is SQL error 823?
It is a problem in SQL Server that can be related to:
- The operating system error
- An IO error
In this article, we will talk about the IO error. The error message will be like this message:
|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
- The error will indicate the data file (usually an .MDF file) and the operation can be read or write. The offset is the physical byte offset from the start of the file. It is calculated as the logical page is the offset number divided 8192.
- SQL Error 832 is the error number. The severity 24 means and implies that it can be media failure and it may be necessary to call the hardware provider and/or restore the database.
- The state 4 is used to differentiate the error from other errors with the same number, but in a different state.
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 in the page and it allows you to detect when the page is not successfully written to disk, but does not tell you 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 try to repair the database using the following sentences:
DBCC CHECKDB (DB_NAME, REPAIR_REBUILD);
The database needs to be in single user mode. To change to single user mode, you can use the following T-SQL sentences:
ALTER DATABASE DB2 SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
Another way to do it is to right-click the database in SSMS and go to the Options page. To Restrict Access, select SINGLE USER:
Run “DBCC CHECKDB” again to verify that the database was repaired:
- If the problem persists, it may be a hardware problem, you may need to repair your hard disk. Contact with the IT members in charge of the hardware. Check also if there are fragmentation problems in the hard disk.
- Another option may be to restore your database with a backup. Once the database is restored, run a CHECKDB to verify that the problem is solved.
- If you have a Torn page error, you can use the CHECKSUM and the torn page detection to repair and verify the errors.
Another option would be to download Stellar Repair for MS SQL. This software can be downloaded from the FREE DOWNLOAD button:
This software will repair the .MDF file. You will need 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.
If you do not know where your data files are, you can use the following T-SQL commands (the SQL Server service needs to be started):
SELECT name, physical_name AS current_file_location FROM sys. master_files
Once that the .MDF file is specified, 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.
The SQL repair tool repairs the .MDF and .NDF files and, you will no longer see the SQL Server error 823.
In this article, we learned different reasons that produced the SQL Server error 823 and how to solve this problem. This problem can be caused by hardware (disk errors, power failures) or system problems. We also show how to solve with a backup, a CHECKDB and finally using Stellar Repair for MS SQL.
Stellar Repair for MS SQL is a simple SQL repair tool. If you have any query, please do not hesitate to write your questions and comments.