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:
Msg 824, Level 24, State 2, Line 2
SQL Server detected a logical consistency-based I/O error: incorrect pageid (expected 1:143; actual 0:9). It occurred during a read of page (1:143) in database ID 39 at offset 0x0000000011e000 in file ‘C:Program FilesMicrosoft SQL ServerMSSQL12.SQL2014MSSQLDATAError1.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 3313, Level 21, State 1, Line 2
During redoing of a logged operation in database ‘Error1’, an error occurred at log record ID (135:752:2). Typically, the specific failure is previously logged as an error in the Windows Event Log service. Restore the database from a full backup, or repair the database.
Msg 3414, Level 21, State 1, Line 2
An error occurred during recovery, preventing the database ‘Error1’ (39:0) from restarting. Diagnose the recovery errors and fix them, or restore from a known good backup. If errors are not corrected or expected, contact Technical Support.
Msg 824, Level 24, State 2, Line 2
SQL Server detected a logical consistency-based I/O error: incorrect pageid (expected 1:160; actual 0:41). It occurred during a read of page (1:160) in database ID 39 at offset 0x00000000140000 in file ‘C:Program FilesMicrosoft SQL ServerMSSQL12.SQL2014MSSQLDATAError1.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 2, Line 4
SQL Server detected a logical consistency-based I/O error: incorrect pageid (expected 1:160; actual 0:41). It occurred during a read of page (1:160) in database ID 39 at offset 0x00000000140000 in file ‘C:Program FilesMicrosoft SQL ServerMSSQL12.SQL2014MSSQLDATAError1.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 ‘Error1’ is name of the corrupt MDF database being repaired.
Msg 3313 indicates a log operation cannot be performed.
Screenshot of error message:
Precise Explanation:
When CHECKDB cannot performa logged operation, it will report the error message Msg 3313 and try to fix the errors. If it cannot fix the error, then the recovery fails and will lead to more errors, such as Msg 3414 and Msg 824.
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 Msg 3313 error:
| SQL Server version | Corrupt MDF file | MDF file fixed by DataNumen SQL Recovery |
| SQL Server 2014 | Error9.mdf | Error9_fixed.mdf |