One of our customer notified us about an SQL Error 824. This article will put your through the steps taken to solve this issue.
Here at Ibmi Media, We have previously assisted numerous customers fix SQL related errors as part of our Server Support Services.
In this context, we shall look into this issue and how to fix it.
What triggers SQL Server Error 824?
When an SQL Error 824 occurs, you will see an error message such as;
2020-07-04 16:46:42.90 spid51 Error: 824, Severity: 24, State: 2.
2020-07-04 16: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.
SQL Server Error 824 is a fatal error which is classed as a logical Input/Output (I/O) error. The function of the I/O is what makes the page readable from the disk.
Therefore, when a database is corrupted as a result of a logical consistency error, the I/O subsystem component will be faulty.
In Microsoft SQL Server, Windows API's such as WriteFileGather, WriteFile, ReadFileScatter and Read-file are used for the execution of I/O operations. As soon as the I/O operations processes are executed, the server checks for errors that might affect these sort of API requests. Sometimes such API calls could fail due to logical inconsistency issues and results to an SQL Server error 823.
Factors that could trigger SQL Server Error 824 includes the following;
i. SQL's file system discrepancies.
ii. When the SQL Server database file is corrupted or damaged.
iii. As a result of driver or Hardware issues in the I/O path.
iv. Due to Storage system issues.
How to fix SQL Server Error 824?
To fix SQL Error 824, first you need to know more about the cause of the error and it is best to inspect the log files. Information such as the database to which the database file belongs, page number of the known I/O operation, details of failed logical consistency checks are very important to resolve this issue.
Below are some tips to solve SQL Server Error 824.
1. Have a in-depth look at the suspect_pages table in msdb for other pages in the same database or different databases which are affected by this issue.
2. Use the built-in DBCC CHECKDB command to check the databases reliability of databases on the same volume.
3. Ensure the the "PAGE_VERIFY CHECKSUM" database option in the database is switched on as when off it could trigger SQL Error 824.
4 Refer to the device or hardware manufacturer to ensure that;
i. The supporting software components and the device drivers of all devices in the I/O path are updated.
ii. The hardware devices and the configuration is compatible with the I/O requirements of the SQL Server.
5. Use the device manufacturer or hardware vendor's diagnostic utilities to verify that the I/O system is in a good condition or not.
6. You can use an SQL database repair tool to repair the corrupted database.
7. You can do a complete restoration of the database from a recent working backup as this is a great way to fix Error 824.