MS SQL Server is a relational database management system by Microsoft designed to store, manage, and restore using Transact-SQL or T-SQL. If you are an expert, you can execute the queries successfully. Still, you cannot rule out the chances of errors completely.
Several types of SQL Server errors may obstruct the transactions in the database, bringing the functioning of the organization to a standstill. These errors can belong to different fields, such as connectivity, T-SQL and query, optimization and performance, installation, configuration, and database admin errors. One such error is MS SQL Server Error 824, which indicates database file corruption due to underlying hardware or storage issues.
Let’s talk about this error in detail and find out several manual and automated ways to resolve it. Among the automated methods, the use of professional SQL recovery software such as Stellar Repair for MS SQL will be a suitable one. We will discuss it as well.
What is MSSQLSERVER_824 Error?
When working with MS SQL Server, you may come across Error 824: I/O Error Detected as shown in the image below:
Here are some crucial points about this SQL Server error
- You can see this message in the SQL Server error log or Windows Application event log.
- It occurs due to a failed logical consistency check when performing the read or write operation in a database file (MDF, NDF, or LDF).
- Hence, it is a logical I/O Error, but can also occur due to storage subsystem or hardware issues.
- The error indicates the detection of corruption when SQL Server tries to read or write data from disk. The detected corruption does not match the expected page structure or checksums.
- When a Data Manipulation Language (DML) query, such as SELECT, INSERT, DELETE, or UPDATE, fetches Error 824, SQL Server may terminate the database connection to avoid further damage to the database.
What Does the MSSQLSERVER_824 Error Message Include?
The information contained in the MSSQLSERVER_824 message can help you understand the error in detail. It will help you to adopt the best method to fix it. Here are the details:
- Database that contains the file
- Database file against which the SQL Server performed the I/O operation
- Database file Offset where the SQL Server performed the operation
- Page number linked with the I/O operation
- Type of operation, whether read or write
- Information about the failed logical consistency check – the type of check, expected and actual value used for the check, etc.
What Are the Possible Causes behind MSSQLSERVER_824
The error happens when Windows does not detect any issue, but SQL Server discovers the fault with the database page. This could happen because of several reasons:
- Problematic underlying storage (I/O) system: Caused due to failing disk drives, faulty device drivers, or hardware failures.
- File system inconsistencies: Where Windows reads disk data successfully, but SQL Server finds logical inconsistencies during the I/O operation.
- Corrupt MDF, NDF, or LDF files: Corruption, such as checksum failure, torn pages, or invalid page header can cause Error 824 when access.
How to Fix MSSQLSERVER_824 Error Manually?
You can try multiple solutions to resolve the error 824. Here is a detailed list of all these methods.
1. Search the suspect pages in the database
Find out if other pages in the same or different databases are facing the same problem. Here is the T-SQL query to serve this task:
SELECT * FROM msdb..suspect_pages
WHERE (event_type = 1 OR event_type = 2 OR event_type = 3);
This query reads the suspect_pages table stored in the msdb system database with the type of page errors you wish to track. Specifically, the column event_type displays the type of issue you want to detect on the target page.
2. Check Databases for Consistency through DBCC CHECKDB
Here is the command for this check operation:
DBCC CHECKDB;
If the check shows inconsistencies with the messages as displayed in Error 824, seek guidance from the Knowledge Base article. It will provide detailed information about resolving the database consistency errors fetched by the DBCC CHECKDB command.
3. Turn on the PAGE_VERIFY CHECKSUM option in the database
If the database shows error 824, you will need to check the PAGE_VERIFY CHECKSUM option. If it is not switched on already, you will have to turn it on. It will help in verifying the page consistency after the SQL Server writes to disk. Use the following script for this purpose:
SELECT * FROM sys.databases
WHERE page_verify_option_desc != ‘CHECKSUM’;
Successful execution of this query will list databases that have CHECKSUM options disabled. Open the database that shows the MSSQLSERVER_824 error and turn the PAGE_VERIFY CHECKSUM option on.
4. Review the Event Logs
It will help to look out for messages or errors from the Windows OS, device driver, or storage device. If you find any connection between these error messages and Error 824, first resolve those errors.
For instance, the disk source may report an event in the event log: “The driver detected a controller error on \Device\Harddisk4\DR4″. In any such case, examine if this file is located on this device and then fix those disk errors.
5. Use SQLIOSim Utility
This utility will help you know if it is possible to reproduce these 824 errors outside regular SQL Server I/O requests. You don’t need to download SQLIOSim separately, as it is available with SQL Server 2008 (10.0.x) and later versions.
6. Assess the Filter Drivers
Look for filter drivers existing on the I/O paths of the requests by running the following Windows system-level commands:
fltmc filters
fltmc instances
Consider these points:
- Do not scan the database and log files with these filter drivers.
- Check for filter driver updates
- Try to remove or disable these filter drivers to check if it can fix Error 824.
7. Updated Hardware Devices
Make sure your hardware devices and configuration fulfill the I/O requirements of SQL Server. Also, ensure to have updated device drivers and other supporting software components in the I/O path.
8. Database Restore
If the error 824 does not relate to hardware issues, it is better to opt for a database restore from a recent backup.
Use professional SQL Recovery Software to fix Error 824
These manual methods may not deliver the expected or accurate results. They take considerable time and need the admin to possess in-depth technical knowledge. A better alternative to resolve MSSQLSERVER_824 Error with the least human intervention and a high success rate is to use a professional SQL recovery software. Notably, Stellar Repair for MS SQL is a renowned application in this line. A trustworthy software, it can resolve all types of SQL Server issues, enabling you to carry out SQL database transactions with ease. Let’s look at its key features:
- Repairs corrupt MDF and NDF database files
- Fixes DBCC CHECKDB consistency errors
- Resolves corruption in the SQL database MDF file header, indicated by error 5172
- Resolves MSSQLSERVER_824 Error
- Repairs Corrupted Index of SQL Server Database
- Retrieves deleted or missing records from SQL databases
- Allows selective recovery of database objects
- Lets you preview repairable MS SQL Database Objects
- Performs parallel processing for quicker restore
- Provides Windows and SQL Server authentication
- Save log reports to analyze the database repair process later
- Restores a corrupt.bak file and restores the SQL Server database
It is easy to install and use the software to speed up the database processes at your end. Follow all the instructions provided in the official installation guide. However, while third-party tools may help to deal with certain corruption scenarios, they are not the replacements for root-cause diagnosis of I/O subsystem and hardware.
You can download the free trial version with limited capabilities. It will enable you to scan & preview recoverable SQL Server database objects. For full-fledged support, prefer to buy Stellar Repair for MS SQL. The three available options of the professional SQL recovery software include Corporate, Technician, and Toolkit.
Conclusion
When working on your MS SQL Server database, you may encounter several types of errors that halt the functioning of your organization if not resolved timely. MSSQLSERVER_824 is one of these alarming errors. Several reasons can lead to this error, such as inconsistencies in the file system, a troublesome storage (I/O) system, or corrupt database files.
Numerous fixes are available to resolve this error. Some of the key resolutions include looking for suspect pages in the database, using DBCC CHECKDB to check database consistency, turn on the PAGE_VERIFY CHECKSUM option in the database, reviewing the error log, and more.
While any of these methods can fix the error 824, there is no guarantee of permanent resolution. Moreover, these fixes are time-consuming, error-prone, and require in-depth technical knowledge. This is the reason admins prefer opting for a third-party alternative in the form of professional SQL recovery software.
This is where Stellar Repair for MS SQL makes a key difference. It can resolve the SQL Server database errors, including Error 824, with ease. Due to its multiple features and quick functioning, it remains a preferred SQL recovery software for IT professionals and organizations. You can try its free version or buy the paid versions based on your suitability.
