Table of Contents
SQL Server Error : 823 Details
SQL Server Error: 823
Severity: 24
Event Logged or not: Yes
Description:
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.
Severity 24 Description:
Indicates a media failure. The system administrator may have to restore the database. You may also have to call your hardware vendor.
Explanation:
Windows APIs are used by SQL Server to conduct file I/O operations (read/write/read/write/read/write/read/write). SQL Server checks for errors related with these API calls after completing these I/O activities. API calls that are terminated due to an operating system issue are reported as Error 823 by the SQL Server database.
When the I/O operation was conducted against a database file The position in the file at which the I/O operation was attempted is the offset. This is the physical byte offset from the file’s beginning.
This value can be divided by 8192 to determine the logical page number that is affected by the error.
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 like error message below:
Error: 823, Severity: 24, State: 2.
2019-08-16 00:11:55.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 files\Microsoft SQL Server\mssql\data\mydb.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.Cause
Problems with storage systems, hardware or drivers in the path of I/O requests are frequently indicated by an 823 error message. This error can occur if the file system is inconsistent or if the database file is corrupted. Before SQL Server returns 823 for a read request, it will have already retried the request four times. In this case, the query will not fail, but message 825 will be recorded to the ERRORLOG and Event Log.
Solution for Resolving the Error
See whether there are more pages that are experiencing this issue in the suspect pages table in MSDB (in the same database or different databases).
DBCC CHECKDB can be used to verify the consistency of databases located on the same volume as the one described in the 823 message. If the DBCC CHECKDB command finds discrepancies then troubleshoot database consistency faults given by DBCC CHECKB.
For any faults or warnings produced by the Operating System, a Storage Device, or Device Driver, check the Windows Event Logs. If they are anyhow related to this error, fix them first. Apart from the 823 message, there may also be an even number displayed on your screen. “The driver detected a controller error on \Device\Harddisk2\DR2” 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.
Find out if these 823 problems can be replicated outside of normal SQL Server I/O requests by using the SQLIOSim program or using replay of a sql trace. No separate SQLIOSim download is required for SQL Server 2008 and subsequent versions. As a general rule, you’ll locate it in the Binn folder of your “C:Program Files (x86)Microsoft SQL Server (x86) (MSSQLSERVER)MSSQL”.
Ensure that the I/O needs of SQL Server are met by working with your hardware vendor or device manufacturer.
For all devices in the I/O path, device drivers and other supporting software components have been updated.
sql server error 823 severity 24 state 2 or sql server error 823 severity 24 state 1 errors can be resolved this way.
Reading sql server error log location for DBCC error messages from SQL Query
Identifying SQL Server Error Log File used by SQL Server Database Engine can be done by reading SQL Server Error Logs. DBA can execute the XP_READERRORLOG extended stored procedure to read the SQL Server Error Log and search for its location used by the instance of SQL Server.
USE master
Go
xp_readerrorlog 0, 1, N'Logging SQL Server messages in file', NULL, NULL, N'asc'
Go
The parameters for XP_READERRRORLOG are:
1. Value of error log file we would like to read. values are 0 = current, 1 = last one before current, 2 = second last before current etc…
2. Log file type:- 1 or NULL = error log, 2 = SQL Agent log
3. Search string 1:- String one you want to search for
4. Search string 2:- String two you want to search for to further refine the results
5. start time for Search
6. end time for search
7. Sort order for search results:- N’asc’ = ascending, N’desc’ = descending
By default, we have 6 Server Error Logs kept but we can increase the number of SQL Server Error Logs from the default value of six.
For other ways to read and find error log location please our artcile https://sqlserver-dba.co.uk/error-log/sql-server-identify-location-of-the-sql-server-error-log-file.html
Alternate errors encountered:
The following messages are all related to this issue and solutions are similar
- sql server error 823 824 and 825
- sql server error 823 dbcc checkdb
- sql server agent alerts errors 823 824 and 825
- sql server error code 823
- error irrecuperable 823 sql server
- sql server error error 823
- sql server fatal error 823 or fatal error 823 in sql server or fatal error 823 sql server 2014 or sql server warning fatal error 823
- error 823 in sql server or error 823 sql server or microsoft sql server error 823 or ms sql server error 823
SQL Server Error Code and solution summary
SQL Server Error: 823
Severity: 24
Event Logged or not: Yes
Description:
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.
DBCC CHECKDB can be used to verify the consistency of databases located on the same volume as the one described in the 823 message. If the DBCC CHECKDB command finds discrepancies then troubleshoot database consistency faults given by DBCC CHECKB.