| title | description | ms.custom | ms.date | ms.service | ms.reviewer | ms.subservice | ms.topic | helpviewer_keywords | ms.assetid | author | ms.author |
|---|---|---|---|---|---|---|---|---|---|---|---|
|
MSSQLSERVER error 823 | mssqlserver_823 |
A description and some common solutions to Microsoft SQL Server Error 823 (mssqlserver_823), which is a severe system-level error condition that threatens database integrity and must be addressed immediately. |
01/27/2019 |
sql |
supportability |
reference |
823 (Database Engine error) |
0d9fce3c-3772-46ce-a7a3-4f4988dc6cae |
MashaMSFT |
mathoma |
[!INCLUDE SQL Server]
Details
| Attribute | Value |
|---|---|
| Product Name | SQL Server |
| Event ID | 823 |
| Event Source | MSSQLSERVER |
| Component | SQLEngine |
| Symbolic Name | B_HARDERR |
| Message Text | The operating system returned error %ls to SQL Server during a %S_MSG at offset %#016I64x in file ‘%ls’. 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. |
Explanation
SQL Server uses Windows APIs (for example, ReadFile, WriteFile, ReadFileScatter, WriteFileGather) to perform file I/O operations. After performing these I/O operations, SQL Server checks for any error conditions associated with these API calls. If the API calls fail with an Operating System error, then SQL Server reports Error 823.
The 823 error message contains the following information:
- The database file against which the I/O operation was performed
- The offset within the file where the I/O operation was attempted. This is the physical byte offset from the start of the file. Dividing this number by 8192 will give you the logical page number that is affected by the error.
- Whether the I/O operation is a read or write request
- The Operating System error code and error description in parentheses
Operating system error: A read or write Windows API call is not successful, and SQL Server encounters an operating system error that is related to the Windows API call. The following message is an example of an 823 error:
Error: 823, Severity: 24, State: 2.
2010-03-06 22:41:19.55 spid58 The operating system returned error 1117 (The request could not be performed because of an I/O device error.) to SQL Server during a read at offset 0x0000002d460000 in file 'e:program filesMicrosoft SQL Servermssqldatamydb.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.
You may or may not see errors from the DBCC CHECKDB statement on the database that is associated with the file in the error message. You can run the DBCC CHECKDB statement when you see an 823 error. If the DBCC CHECKDB statement does not report any errors, you probably have an intermittent system problem or a disk problem.
Additional diagnostic information for 823 errors may be written to the SQL Server error log file when you use trace flag 818.
For more information, see KB 826433: Additional SQL Server diagnostics added to detect unreported I/O problems
Cause
The 823 error message usually indicates that there is a problem with underlying storage system or the hardware or a driver that is in the path of the I/O request. You can encounter this error when there are inconsistencies in the file system or if the database file is damaged. In the case of a file read, SQL Server will have already retried the read request four times before it returns 823. If the retry operation succeeds, the query will not fail but message 825 will be written into the ERRORLOG and Event Log.
User Action
- Review the suspect_pages table in MSDB for other pages that are encountering this problem (in the same database or different databases).
- Check the consistency of databases located on the same volume (as the one reported in the 823 message) using DBCC CHECKDB command. If you find inconsistencies from the DBCC CHECKDB command, use the guidance from How to troubleshoot database consistency errors reported by DBCC CHECKB.
- Review the Windows Event logs for any errors or messages reported from the Operating System or a Storage Device or a Device Driver. If they are related to this error in some manner, address those errors first. For example, apart from the 823 message, you may also notice an event like «The driver detected a controller error on DeviceHarddisk4DR4» reported by the Disk source in the Event Log. In that case, you have to evaluate if this file is present on this device and then first correct those disk errors.
- Use the SQLIOSim utility to find out if these 823 errors can be reproduced outside of regular SQL Server I/O requests. The SQLIOSim utility ships with SQL Server 2008 and later versions so there is no need for a separate download. You can typically find it in your
C:Program FilesMicrosoft SQL ServerMSSQLxx.MSSQLSERVERMSSQLBinnfolder. - Work with your hardware vendor or device manufacturer to ensure
- The hardware devices and the configuration conforms to the I/O requirements of SQL Server
- The device drivers and other supporting software components of all devices in the I/O path are up to date
- If the hardware vendor or device manufacturer provided you with any diagnostic utilities, use them to evaluate the health of the I/O system
- Evaluate if there are filter drivers that exist in the path of these I/O requests that encounter problems.
- Check if there are any updates to these filter drivers
- Can these filter drivers be removed or disabled to observe if the problem that results in the 823 error goes away
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.
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 FilesMicrosoft SQL Server MSSQLDATAdb.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.
About The Author
Priyanka
Priyanka is a technology expert working for key technology domains that revolve around Data Recovery and related software’s. She got expertise on related subjects like SQL Database, Access Database, QuickBooks, and Microsoft Excel. Loves to write on different technology and data recovery subjects on regular basis. Technology freak who always found exploring neo-tech subjects, when not writing, research is something that keeps her going in life.
Best Selling Products
Stellar Repair for MS SQL
Stellar Repair for MS SQL is an enterpri
Read More
Stellar Toolkit for MS SQL
3-in-1 software package, recommended by
Read More
Stellar Converter for Database
Stellar Converter for Database is an eff
Read More
Stellar Repair for Access
Powerful tool, widely trusted by users &
Read More
Insights solution for Microsoft SQL Server Error 823
SQL Server application is a commonly used database management program and is part of many organizations. Microsoft SQL Server error 823, mostly occur due to hardware problems and other system issues. It indicates that storage system, hardware, any driver, which is in the path of the I/O operation path, has some problem. This problem can be severe as the data entities and databases are important for users. The error can be caused due to several reasons and if you too are getting this error issue, then the most obvious reason for it is hardware or system errors while reading or writing data into the database files.
What Causes Microsoft SQL Server 823 occurs?
SQL Server utilizes Windows API’s like ReadFile, ReadFileScatter, WriteFile, WriteFileGather etc. in order to perform the I/O operations. Once these operations are performed the server application will check if there is any error issues allied to these API calls. In case these API operations are failed throwing an OS error, then the SQL Server will display SQL Error 823. The details of the 823 error message are like below;
SQL Error 823:
This error is explained and elaborated below;
- error: It can be either OS error or I/O check failure error. For OS error, error number follows the I/O error.
- In case the error is related to I/O operation, then the failure message in the parentheses can be one amongst the below mentioned message;
- bad page ID: This message indicates that pageID on the header of page is not the page which was supposed to be read from disk.
- torn page: If the page is torn and has corruption issues in it then this message is displayed.
- insufficient bytes transferred: Such message refers to Windows API was done well but incorrect or insufficient bytes were transferred.
- operation: Operation made is Write operation or Read.
- offset: This represents the physical byte offset. You can divide this number by 8192 which will give the logical page number which is corrupted.
- file: This represents the name of the file which is having the problem.
Note: Different versions of SQL Server can have different error message formats. But the reasons and troubleshooting methods are same for all the versions.
Resolutions for Various Types of Errors
Type #1: Operating System Error:If any read/write Windows API call is failed, SQL server comes across this OS error. This can lead to another Microsoft SQL server error 823 for the Operating system. This error message appears like;
Resolution: The Operating System issue reported as SQL Error 823 is associated with the system problem. In case OS operation is the root cause for error, DBCC CHECKDB statement execution will not report any problem. In that case it is suggested to contact the hardware vendor, Microsoft Support Service, or System Administrator in order to sort out the problem. It is advised to run the DBCC CHECKDB on the SQL Server databases and if it doesn’t report any problem, then it is quite evident that the system or disk has some issues with it.
Type #2: I/O Logical Check Failed: In case a read/write Windows API call for database is done without any issue, but a particular logic checks on data in unsuccessful due to any index corruption or torn page this 823 error can occur. Below mentioned error is an example for the I/O logical error.
Resolution: If any such error comes across, run DBCC CHECKDB statement on that databases which is mentioned in the error message. If the statement displays any error then you can rectify those errors. In case the error still occurs on accessing the SQL database or if any error is not reported by DBCC CHECDB statement, then users can analyze the event log of the system to check for any disk errors occurred prior running the server. One can also contact the hardware vendor for diagnosing the problem.
Conclusion
SQL Server has this inbuilt integrity check tool DBCC CHECKDB to check in the integrity of the databases. It is highly recommended to run this tool whenever any error message occurs while accessing the database. In case any report is displayed, you can resolve the error and if none of such report is generated you can go for other resolutions or you can try SQL MDF recovery tool to get rid of this I/O logical error. Microsoft SQL Server Error 823 represents that either the problem is with the Operating System or the problem is with the I/O operation.
- Remove From My Forums
-
Question
-
Hi Gurus,
When am connecting to SQL Server instance am getting below error message.
Error Message:
Warning: Fatal error 823 occurred at Apr 20 2015 8:04PM.Note the error and time, and contact your system administrator. (.Net SqlClient Data Provider).
When checked in configuration manager all services are running only.
Kindly help me to resolve this issue as soon as possible.
Thanks all in advance.
-
Edited by
Monday, April 20, 2015 3:15 AM
-
Edited by
Answers
-
Below is the text for error 823 from sys.messages. The details should be in the SQL Server error log. Follow the instructions in the error message.
«The operating system returned error %ls to SQL Server during a %S_MSG at offset %#016I64x in file ‘%ls’.
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.»
Dan Guzman, SQL Server MVP, http://www.dbdelta.com
-
Proposed as answer by
Lydia ZhangMicrosoft contingent staff
Tuesday, April 21, 2015 7:26 AM -
Marked as answer by
Lydia ZhangMicrosoft contingent staff
Monday, April 27, 2015 9:04 AM
-
Proposed as answer by
-
Satish this might not be true for your case, this is a general error message and can be caused by many things. please check windows event viewer and SQL errorlog to see if you find anything during that time frame when this error ccurred.
-
Proposed as answer by
Lydia ZhangMicrosoft contingent staff
Tuesday, April 21, 2015 7:26 AM -
Marked as answer by
Lydia ZhangMicrosoft contingent staff
Monday, April 27, 2015 9:04 AM
-
Proposed as answer by