SQL Server Error 824 – Troubleshooting Guide
Table of Content
Brief Overview
SQL server is a highly popular database management system and despite being efficient in its functioning makes the user encounter some errors during its usage. One such error is the SQL Server Error 824 which takes place due to logical consistency and is a fatal error.
SQL server database 824 error means that the page has been read successfully from the disk but there is some issue with the page. Other types of messages that might be shown are “fatal error 824 occurred” , “SQL server detected logical consistency based i/o error”. The exact display message can be more helpful in understanding this problem.
SQL Server Error 824 – Display Message
You may be shown the following error message when SQL server logical consistency error occurs in the database.
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.
‘E:\MSSQL.SQL2010\MSSQL\DATA\abc_d.mdf’
2017-09-10 01:30:12.80 spid50
Error: 824, Severity: 24, State: 2.
2017-09-10 01:30:12.80 spid50
SQL Server detected Logical Consistency-Based I/O error: incorrect page-id (expected 1:43601; actual 0:0)
Quick Solution: For recovering from fatal error 824 easily and for continuing usage of database services seamlessly use an automated software such as SQL Database Repair Tool. This way users can solve various issues including SQL server login failed error 233 as well.
Possible Causes for SQL Server Error 824
SQL server error message 824 is displayed while using SQL Server due to several reasons which are given as follows
- Insufficient storage space on disk can cause error code 824
- Hardware device or driver software which acts as a medium may cause errors
- File system inconsistency can also be reason for SQL server logical consistency error
- Damage caused to the database files may be a prime cause for the problem as well
- Corruption of the file system can also give rise to SQL fatal error 824
SQL server database 824 error may cause damage to the integrity of the database. Thus it is wise to perform consistency check of the entire database using inbuilt utility or repair file corruption through automated software.
Workarounds to Resolve SQL Server Error 824 Manually
There are some manual techniques which can help to troubleshoot fatal error 824. Follow the different inbuilt methods given below
Tip #1: Carefully examine the suspect_pages table present in the database and find out if other pages are facing the same problem
Tip #2: Check the state of consistency of the SQL server database which is present in the same volume. You can do this using the DBCC CHECKDB command.
Tip #3: Turn on the PAGE_VERIFY CHECKSUM database option if it is not already activated
Tip #4: Find out if any error has occurred through the OS, storage devices, or device drivers by checking the Windows event logs. Correct the errors, if any.
Tip #5: SQLIOSim method can also be useful in finding out the status of SQL server database 824 error
If you don’t have backup created before attempting to resolve SQL server error 824, follow the steps given below instead
- Stop all SQL Services
- Copy and Save the corrupted SQL Server database at a new memory location
- Start all the SQL Services
- Create a blank SQL database having the same name
- Separate your blank database from the original database.
- Run the query given below after performing the above-mentioned steps
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
Problems with Manual Workarounds
- They are complex to follow and implement by users having limited technical expertise
- A large amounts of time is required to be spent to properly resolve SQL fatal error 824
- There is a risk of permanent database damage and if steps are not followed carefully.
Conclusion
The error 824 in SQL server can cause several problems to DBAs and to solve it is a major concern. Manual techniques are available but they can cause further troubles during usage. So to restore database to a normal state & to get rid of SQL server error 824, use an automated software solution.