SQL error 824 happens when there exist logical inconsistency. It is a fatal error which disrupt the functioning of the SQL Server.

Here at Ibmi Media, as part of our Server Management Services, we regularly help our Customers to fix SQL related issues.

In this context, we shall look into the steps to tackle this SQL error.

Nature of 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-07-03 15:46:42.90 spid51 Error: 824, Severity: 24, State: 2.
2020-07-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 triggers SQL error 824?

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;

i. Insufficient storage capacity on the hard drive.

ii. Software or hardware failure.

iii. Corrupt database files are the main reason for the problem.

iv. Discrepancies in the SQL's file system.

Tips to fix SQL error 824?

To fix this SQL error, simply the steps provided below;

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;

i. Look for any updates to these filter drivers

ii. 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 are available 24*7]