Detailed Information about "File Appears To Have Been Truncated By The Operating System" Error

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 5125, Level 24, State 2, Line 2
File ‘C:Program FilesMicrosoft SQL ServerMSSQL12.SQL2014MSSQLDATAError1.mdf’ appears to have been truncated by the operating system. Expected size is 5120 KB but actual size is 5112 KB.
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.

where ‘Error1’ is name of the corrupt MDF database being repaired.

Msg 5125 error is neigher an allocation error nor a consistency error. It occurs when the part of the tail of the MDB file is removed, due to data corruption.

Screenshot of error message:

Precise Explanation:

When the part of the tail of the MDB file is removed, CHECKDB will report Msg 5125 error and try to fix it. If the database cannot be fixed, then it will produce Msg 3414 error.

Actually the database can also be recovered, if using our product DataNumen SQL Recovery to do the task.

Sample Files:

SQL Server version	Corrupt MDF file	MDF file fixed by DataNumen SQL Recovery
SQL Server 2014	Error8.mdf	Error8_fixed.mdf

References:

• https://docs.microsoft.com/en-us/sql/relational-databases/errors-events/mssqlserver-2512-database-engine-error?view=sql-server-ver15
• https://docs.microsoft.com/en-us/sql/relational-databases/errors-events/mssqlserver-3414-database-engine-error?view=sql-server-ver15