SQL error 824 occurs due to logical inconsistency and is a fatal error.

Here at Bobcares, we have seen several such SQL related issues as part of our Server Management Services for web hosts and online service providers.

Today we’ll take a look at the cause for this error and see how to fix it.

More about SQL error 824

SQL error code 824 is a logical Input/Output error. It means that the disk successfully reads the page. However, there is some sort of issue on the page that leads to this error.

Here is an error message that appears in the SQL Server error log or the Windows Application event log.

~~
2020-09-03 15:46:42.90 spid51 Error: 824, Severity: 24, State: 2.
2020-09-03 15:46:42.90 spid51 SQL Server detected a logical consistency-based I/O error: incorrect pageid (expected 1:43686; actual 0:0). It occurred during a read of page (1:43686) in database ID 23 at offset 0x0000001554c000 in file ‘H:\MSSQL.SQL2008\MSSQL\DATA\my_db.mdf’. Additional messages in the SQL Server error log or system event log may provide more detail. This is a severe 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.
~~

What causes SQL error 824 to occur

Here are the different causes of this error to occur.

To execute I/O operations, SQL servers use Windows APIs(ReadFile, WriteFile, ReadFileScatter, WriteFileGather).
After the execution of the I/O operation, the server checks for any errors related to APIs. Sometimes, the Windows API call actually succeeds but the data transferred by the I/O operation encounters a logical consistency problem. These logical consistency problems are reported via Error 824.

• Insufficient storage capacity on the hard drive
• Software or hardware failure
• Corrupt database files are the main reason for the problem.
• Discrepancies in the SQL’s file system

How we fix SQL error 824

Here are the steps that our Support Engineers follow to resolve this error.

1. Check the suspect_page table in msdb to verify that other pages are not facing the same error.

2. Using inbuilt DBCC CHECKDB command, check the state of consistency of the SQL server database that is located in the same volume.

3. Turn on the PAGE_VERIFY CHECKSUM database option. Because CHECKSUM provides the best option to verify the consistency of the page after it has been written to disk.

4. Check the Windows Event logs for any errors or messages reported from the Operating System or a Storage Device or a Device Driver.

5. Make use of SQLIOSim utility so that you can find out if these 824 errors can be reproduced outside of regular SQL Server I/O requests. SQLIOSim ships with SQL Server 2008 so there is no need for a separate download on this version or later.

6. Contact your hardware vendor to confirm the below:

• The hardware devices and configuration is compatible with the I/O requirements of SQL Server.
• Make sure that the device drivers and other supporting software components of all devices in the I/O path are updated.

7. In case, if the hardware vendor provides you any diagnostic utilities then make use of them to evaluate the health of the I/O system.

8. Check if there are any Filter Drivers that are present in the path of these I/O requests that encounter problems.

• Look for any updates to these filter drivers
• Check if these filter drivers are removed or disabled to see if the problem that results in the 824 error fixes.

9. If the problem is not relating to hardware and if a known clean backup is available then try restoring the database from the backup.

[Need any further assistance in fixing SQL errors? – We’re available 24*7]

Conclusion

In short, 824 error is a logical Input/Output (I/O) error. Today, we saw how our Support Engineers fix this SQL error.