Error 824 is a logical IO error. Logical IO error means that the page is read from the disk successfully, but there is something wrong with the page. The error-824 caused due to many reasons, some are:
- Problem with underlying storage system.
- The hardware or a driver that is in the path of the I/O request.
- There are inconsistencies in the file system.
- The database file is damaged.
- File system corruption.
Here is more explanation with a live example of error-824:
Msg 824, Level 24, State 2, Line 1.
SQL Server detected a logical consistency-based I/O error: incorrect checksum (expected: 0x7532c420; actual: 0x320e4240). It occurred during a read of page (1:342) in database ID 15 at offset 0x00000020e24000 in file ‘D:\Data\SomeDB.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 detected a logical consistency-based I/O error: incorrect checksum (expected: 0x7532c420; actual: 0x320e4240). It occurred during a read of page (1:342) in database ID 15 at offset 0x00000020e24000 in file ‘D:\Data\SomeDB.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.
A logical consistency error is a clear indication of actual damage and frequently indicates database corruption caused by a faulty I/O subsystem component.
It can be fixed without data loss, you only need to refer to your backups and perform a restore.
Follow these simple steps, if you don't have updated backup:
- Stop SQL Service.
- Copy and Save your corrupted SQL Server database.
- Now Start SQL Service
- Create an empty SQL Server database with the same name.
- Detach the empty database and Replace it with the corrupted ones in the SQL instance folder or where you keep your databases.
After applying the above steps run the query given below:
EXEC sp_resetstatus 'yourDBname' ;
ALTER DATABASE yourDBname SET EMERGENCY
DBCC checkdb('yourDBname ')
ALTER DATABASE yourDBname SET SINGLE_USER WITH ROLLBACK IMMEDIATE DBCC CheckDB ('yourDBname' , REPAIR_ALLOW_ DATA_LOSS) ALTER DATABASE yourDBname SET MULTI_USER
ALTER DATABASE yourDBname SET EMERGENCY
DBCC checkdb('yourDBname ')
ALTER DATABASE yourDBname SET SINGLE_USER WITH ROLLBACK IMMEDIATE DBCC CheckDB ('yourDBname' , REPAIR_ALLOW_ DATA_LOSS) ALTER DATABASE yourDBname SET MULTI_USER
In case the following procedure won't work then it is recommended to go for third party SQL recovery software to resolve Error-824 in SQL Server Database.
Excellent post, i was searching for this solution, and this post showed me the way.
ReplyDeleteThanks!
Wonderfull! After hours of browsing internet, here I found my happiness!
ReplyDeleteThank you Adam and long life to you!
Very good.. working fine.. Thanks
ReplyDeleteNaveen
Siva Krishna
great post. its solved my problem.
ReplyDeleteI can't create the blank DB with the same name while the corrupted DB is attached. I am following all the steps.
ReplyDeleteAre you getting any error message?
DeleteIf yes, please share
I've received this message after apply the script:
ReplyDeleteMsg 824, Level 24, State 2, Line 1
SQL Server detected a logical consistency-based I/O error: incorrect checksum (expected: 0xe7f81da4; actual: 0xe7f81d48). It occurred during a read of page (1:1538) in database ID 8 at offset 0x00000000c04000 in file 'E:\ASIASOFT\Backup\NSQNNew.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.
Msg 7909, Level 20, State 1, Line 1
The emergency-mode repair failed.You must restore from backup.
Thanks for writing!!!
DeleteI tried your suggestion again and again but did not work for me. Where can I get registration key of Stellar Phoenix SQL database repair software?
ReplyDeleteHi Dear,
ReplyDeleteThanks for writing!!!
As Stellar Phoenix SQL database repair software is a shareware program so you can see the preview of your corrupt data file with the help of demo version. To save the repaired database you need registration key. You can get registration key after purchasing the software.
Buy Software from here: http://www.stellarinfo.com/database-recovery/sql-recovery/buy-now.php
I got the exactly same error message and recovered the database from backup. I created a new blank database and restored database from backup then after I updated the data in the new database from corrupt database. At last I replaced the corrupt database with newly created. Thanks a lot for the article.
ReplyDeleteHi Aaron,
DeleteI am glad to know that it helps you.