Symptom:
When using DBCC CHECKDB with REPAIR_ALLOW_DATA_LOSS parameter to repair a corrupt .MDF database, like this:
DBCC CHECKDB(xxxx, ‘REPAIR_ALLOW_DATA_LOSS’)
you see the following error message:
DBCC results for ‘xxxx’.
CHECKDB found 0 allocation errors and 0 consistency errors in database ‘xxxx’.
Msg 824, Level 24, State 2, Line 8
SQL Server detected a logical consistency-based I/O error: incorrect checksum (expected: 0xea8a9a2f; actual: 0x37adbff8). It occurred during a read of page (1:28) in database ID 39 at offset 0x00000000038000 in file ‘xxxx.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.
where ‘xxxx.mdf’ is name of the corrupt MDF file being repaired. Although CHECKDB says
CHECKDB found 0 allocation errors and 0 consistency errors in database ‘xxxx’.
This is still a consistency error(Msg 824) in the database.
Screenshot of error message:
If the corruption is severe, then there will be continuous error messages(Msg 824), as below:
Msg 824, Level 24, State 6, Line 2 SQL Server detected a logical consistency-based I/O error: incorrect checksum (expected: 0x3d17dfef; actual: 0xd81748ef). It occurred during a read of page (1:0) in database ID 39 at offset 0000000000000000 in file ‘xxxx.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 824, Level 24, State 6, Line 4 SQL Server detected a logical consistency-based I/O error: incorrect checksum (expected: 0x3d17dfef; actual: 0xd81748ef). It occurred during a read of page (1:0) in database ID 39 at offset 0000000000000000 in file ‘xxxx.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.
where ‘xxxx.mdf’ is name of the corrupt MDF file being repaired.
Screenshot of error message:
If the corruption is more severe, you may see Msg 7909 follows Msg 824:
DBCC results for ‘xxxx’.
CHECKDB found 0 allocation errors and 0 consistency errors in database ‘xxxx’.
Msg 824, Level 24, State 2, Line 8
SQL Server detected a logical consistency-based I/O error: incorrect checksum (expected: 0xcfcd2118; actual: 0x6fc599d6). It occurred during a read of page (1:1) in database ID 39 at offset 0x00000000002000 in file ‘xxxx.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 8
The emergency-mode repair failed.You must restore from backup.
where ‘xxxx’ is the database name and ‘xxxx.mdf’ is the database physical file name.
Note Msg 7909 is a severe error that may occurs in many situations whenever SQL Server think the database is beyond recovery.
Screenshot of error message:
Precise Explanation:
The data in MDF file are stored as 8KB pages. Each page has an optional checksum field.
If DBCC CHECKDB command finds the checksum values in the header page, PFS page and some of the data pages are invalid and it cannot correct the problem, then it will report this error(Msg 824). If the corrupton is severe, there may be continuous erros(Msg 824) or followed by another error(Msg 7909).
You can use our product DataNumen SQL Recovery to recover the data from the corrupt MDF file and solve this error.
Sample Files:
Sample corrupt MDF files that will cause the error(Single Msg 824 error):
| SQL Server version | Corrupt MDF file | MDF file fixed by DataNumen SQL Recovery |
| SQL Server 2014 | Error1_3.mdf | Error1_3_fixed.mdf |
Sample corrupt MDF files that will cause the error(Continuous Msg 824 errors):
| SQL Server version | Corrupt MDF file | MDF file fixed by DataNumen SQL Recovery |
| SQL Server 2014 | Error1_1.mdf | Error1_1_fixed.mdf |
Sample corrupt MDF files that will cause the error(Msg 824 error followed by Msg 7909 error):
| SQL Server version | Corrupt MDF file | MDF file fixed by DataNumen SQL Recovery |
| SQL Server 2014 | Error1_2.mdf | Error1_2_fixed.mdf |