How to Troubleshoot Microsoft SQL Database Error 824?

SQL database error 824 can render the database’s MDF and NDF files inaccessible, preventing you from accessing the objects stored in the database. You must troubleshoot the SQL server error 824 immediately to continue working on the database without any interruption or data loss.

SQL Database Error 824: Error Message & Description

Error Message:

Msg 824, Level 24, State 2, Line 1.

Description:

The SQL Database error 824 is a logical Input/Output (I/O) error. A logical I/O means that the page is successfully read from the disk. However, there’s an error in the page itself. Moreover, a ‘logical consistency error’ clearly indicates damage due to corruption in the database where corruption is due to an I/O subsystem component that is faulty.

The SQL Server error 824 contains several  information that are as follows:

• The database to which the database file belongs.
• The database file against which the I/O operation is performed.
• The offset with the database file where the I/O operation was attempted.
• The page number associated with the I/O operation.
• Information whether the operation was ‘read’ or ‘write’.
• Particulars of failed logical consistency check. (The particular are: Type of check, actual value, and expected value used for this check.)

Note – If you come across this SQL database error 824, while raising a query or modifying data, the application is returned the error message, and the database connection is dismissed.

What Causes SQL Database Error 824?

For executing I/O operations, Microsoft SQL Server uses Windows API’s, such as ReadFile, WriteFile, ReadFileScatter, and WriteFileGather. After executing these I/O operations, the server checks for errors related to these API calls. If the API calls that have been stated here fail with an Operating System error, then the SQL Server reports error 823. There are circumstances when the ‘Windows API call’ is successful but the data moved by the I/O operation has met with logical consistency issues. Further, these issues are reported through SQL Server error 824.

Following are some other reasons responsible for SQL Server error 824:

1. Issues in the underlying storage system
2. Hardware or driver issue in the I/O path
3. Corrupt or damaged SQL Server database MDF or NDF file
4. Discrepancies in the SQL’s file system

Troubleshooting SQL Database Error 824

You can try to fix SQL database error 824 by using:

• Manual Approach
• Software Approach

Manual Approach

1. Check the suspect_pages table in msdb to check if other pages in the same database or different databases are encountering this error.
2. With the built-in DBCC CHECKDB command check the reliability of the databases that are located in the same volume (as the one stated in the 824 message). If you find discrepancies on using this command, then troubleshoot the reported database consistency errors.
3. If the PAGE_VERIFY CHECKSUM database option in database is not switched on, do it as soon as possible.

Note: SQL error 824 can occur due to other reasons than a checksum failure. However, CHECKSUM offers you to validate a page’s consistency after it has been written on to the disk.

4. Use the SQLIOSim utility to check if the SQL 824 error can be replicated outside of regular I/O requests in SQL Server.

Note – SQL Server 2008 comes with this utility. For another SQL version, you need to download it from the official website.

5. Work with the vendor of your hardware or device manufacturer to ensure the following:
   • The ‘device drivers’ and other ‘supporting software components’ of all devices in the I/O path are updated and efficient.
   • The hardware devices and the configuration approve to the I/O requirements of SQL Server.
6. If the hardware vendor or device manufacturer has provided you with diagnostic utilities, you should use them to assess if the I/O system is in working condition or not.
7. Assess if there are ‘Filter Drivers’ existing in the path of I/O requests that face issues. To do so, check the following:
   • If there is any update to these ‘filter drivers.’
   • Can these ‘filter drivers’ be disabled or removed to observe if the issue that results in the SQL error 824 is fixed.

With these resolutions, you can troubleshoot SQL error 824. However, there are some disadvantages associated with these methods. All of them being manual, are lengthy and will consume a lot of your time. Plus, they involve risk of data loss and as well as failure due to lack of technical expertise.

Software Approach

Another alternative to fix SQL Server error 824 is to use SQL database repair software, as it helps repair the database to resolve SQL errors while reducing human intervention.

Recommended by Microsoft MVPs and DBAs, Stellar Repair for MS SQL is one software you can rely upon to resolve almost every issue that you can face while working with SQL Server database. Designed to repair the damaged SQL database (MDF) file successfully, the software performs the following as well:

• Recovers objects, such as tables, triggers, keys, rules, indexes, defaults, and other databases objects
• Recovers ‘deleted table records’ from the SQL Server
• Allows selective recovery of database objects
• Provides preview facilities of the database objects before saving them
• Permits to save the repaired file in MS SQL (MDF), XLS, HTML, and CSV file formats
  • Users can save the SQL database as a New or Live database under the MDF option.
• Supports SQL Server 2019, 2017, 2016, 2014, 2012, 2008, and all older editions

End Note

As this blog suggests a number of manual methods and as well as an automatic way to fix error 824 in SQL database, you can use any. However, for quick and successful results you should go for Stellar Repair for MS SQL – an external but result-oriented software!

Was this article helpful?

YES0

NO

About The Author

Jyoti Prakash

Jyoti Prakash is a Senior Manager at Stellar Information Technology Pvt. Ltd., having over 15+ years of experience with a background in information technology. A tech enthusiast and expert, he specializes in data recovery, & file repair. He has participated for numerous communities, including Microsoft, SpiceWorks etc. He also provide training on Windows, Microsoft Office, Online Marketing, & social media.