SQL Server is the most common type of database management system in these days which provides flexibility to the DBA’s to manage their database. But in the SQL Server databases the logical consistency based i/o error is a huge problem. The logical consistency problems/fatal error comes with Microsoft SQL Server Error 824.
SQL logical consistency based i/o error means that the page has been read successfully from the disk, but there is a problem with the page. You might also face ‘fatal error’ in the SQL Server error log or the Windows application event log. The supplementary messages of the SQL Server error log or system event log may provide more details about the logical consistency based error.
The problem of fatal error or logical consistency based i/o error occurred during a read of page (2:53686) in database ID 33 at offset 0x0000001554c000 in file.
'F:\MSSQL.SQL2010\MSSQL\DATA\my_db.mdf'
2010-09-12 01:35:12.80 spid50
Error: 824, Severity: 24, State: 2.
2010-09-12 01:35:12.80 spid50
SQL Server detected Logical Consistency-Based I/O error: incorrect page-id (expected 1:43686; actual 0:0).
SQL Server logical consistency based i/o error 824 may harm the database integrity. In such situation perform the consistency check of whole database using DBCC CHECKDB. Microsoft SQL Server Error 824 may occur due to many reasons such as hardware failure, disk subsystems etc.
When the logical consistency check fails during inserting data in a database or updating the database, then database shows an error message and return to the data and the database connection terminated.
Reasons of Microsoft SQL Server Fatal Error 824
Windows API's like ReadFile; ReadFileScatter; WriteFile; WriteFileGather; is mainly used by the SQL Server to perform the various I/O operations. SQL Server reports the error message 823, at that time when the windows API’s calls failed.
It is possible that the Windows API call successfully, but the data transferred at that time may have logical consistency errors. The logical consistency error is reported as “Microsoft SQL Server Error 824”.
There are also some other reasons behind this error, these are as given below:
- The raising of error code 824 may the insufficient disk storage space.
- The hardware or driver which is in the I/O path may also create problems.
- The inconsistencies of the file systems also a reason of SQL Server Error Code 824.
- The damaged database file.
- File system corruption may create the error 824.
Containing Information by Logical Consistency Error
- Page read error in the database, e.g. (2:53686).
- The Page - ID error means that the requested page is not fetched from the disk, e.g. (expected 1:43686; actual 0:0).
- Severity level 24 shows it may be a fatal error related to the hardware.
- State 2 in SQL Server error message, for the database developers to identify and diagnose the error in code, from where system errors were raised.
- Line 1 shows that the error occurred is in which line.
Troubleshooting the Microsoft SQL Server Error 824
- Please go through the suspect_pages table into the database to check if other pages are encountering this problem.
- It is recommended that please check the consistency state of the databases which are situated in the same volume, using the DBCC CHECKDB command.
- Please check that the PAGE_VERIFY CHECKSUM database option is on if not then, turn it on.
- If any error occurs from the operating system, device driver or a storage device then check the Windows event logs. If the errors associated with these issues, then correct it.
- The SQLIOSim technique can also help to find out the logical consistency error 824.
The logical consistency based errors are regularly corruption issues which occurs due to inappropriate I/O subsystems. This error can be repaired without data loss if you have a complete database backup plans.
If you don’t have database backup plans, then follow the given certain lines:
- Stop the SQL Services
- Copy and Save the damaged SQL Server database at new location
- Start the SQL Services
- Create a blank database with the same name
- Separate the empty database with orignial database.
After doing this run the given below query
EXEC sp_resetstatus ‘db_name’ ;
ALTER DATABASE db_name SET EMERGENCY
DBCC CHECKDB(‘db_name‘)
ALTER DATABASE db_name SET SINGLE_USER Mode
With ROLLBACK IMMEDIATE DBCC CHECKDB (‘db_name’ , REPAIR_ALLOW_ DATA_LOSS)
ALTER DATABASE ‘db_name’ SET MULTI_USER
If you are still getting the error messages and unable to restore the database, then in such situation you should go for a third party SQL repair tool to fix the problem.
Conclusion
In this article we have discussed about the logical consistency based i/o error and different methods to fix the error. Error message 824 associated with the SQL Server database also known as a fatal error.
EmoticonEmoticon