Summary: Corruption in a transaction log file (.ldf) file makes the SQL Server database inaccessible. This blog outlines the possible reasons behind log file corruption and errors you may encounter because of corruption. Further, it describes methods to repair a SQL Server database with a corrupt log file. These methods include restoring the database from backup, rebuilding a corrupt log file, and using specialized SQL recovery software.
A database in SQL Server comprises three types of files: primary data file (.mdf), secondary data file (.ndf), and transaction log file or log database file (.ldf).
The primary and secondary data files are used for storing information about database objects like tables, indexes, triggers, views, etc. On the other hand, log file records all transactions and changes made to the database by each transaction. Corruption in any of these data files or the log file can make the database inaccessible. In this blog, however, we will restrict our discussion on log file corruption and methods to fix a corrupt .ldf file.
Before discussing methods to fix a SQL database with a corrupt log file, it is important to determine the reasons that led to such an issue in the first place.
Some possible reasons that may result in log file corruption are as follows:
Error 1 – Error Message 15105
Operating system error 23 (failed to retrieve text for this error. Reason: 15105) on file “C:\Program Files\Microsoft SQL Server\MSSQL10_50.MYSERVER\MSSQL\DATA\MY_DATABASE.ldf” during CheckLogBlockReadComplete
Error 2 – Failed to Attach Log File
When trying to attach a log file on a new SQL Server, you may receive an error:
“Could not open new database ‘Your_DatabaseName’. CREATE Database is aborted”.
Error 3 – File Activation Failure (Location File Error)
File activation failure. The physical file name ‘C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\XXX.ldf’ may be incorrect. Diagnose and correct additional errors, and retry the operation.
FileMgr::StartLogFiles: Operating system error 2 (The system cannot find the file specified.) occurred while creating or opening file ‘E:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\xxx.ldf’. Diagnose and correct the operating system error, and retry the operation.
NOTE: Check the SQL Server error logs and event logs of the Windows system and application hosting the database. If you find any hardware problem, get it fixed and see whether the corruption issue has been resolved or not. If not, apply the following methods to repair the damaged log file.
NOTE: If you don’t have a proper backup strategy configured or the backup files are lost, skip to method 2.
The simple and safe approach to fix log file corruption issue is restoring the database from the last healthy, point-in-time, database backup.
Let’s look at the steps to restore SQL Server database from full database backup:
Try rebuilding the corrupt log file to make the database accessible again. To do so, follow these steps:
ALTER DATABASE SET EMERGENCY, SINGLE_USER GO
Running the above command will help bring the database up without a transaction log file.
NOTE: Before proceeding with the repair process, make sure to create backup copies of your database. This is because the REPAIR_ALLOW_DATA_LOSS option involves data loss risk.
DBCC CHECKDB ('TestTRNLogCorrupt', REPAIR_ALLOW_DATA_LOSS) If corruption is still there then, use the following command to rebuild the log file. Bring the database in Offline mode and change the name of the corrupted log file associated with it. Now, run the following command:
ALTER DATABASE [original_log_file_name] REBUILD LOG ON (NAME= logicalname, FILENAME='C:\Program Files\Microsoft SQL Server\MSSQLn.MSSQLSERVER\MSSQL\DATA\file_name.ldf')
If you still cannot access your database, using SQL database repair tool may help. The software repairs the .mdf file and creates a new log (.ldf) file.
Steps to use Stellar Repair for MS SQL Software
NOTE: If you are not aware of the MDF file location, click ‘Search’ to find and select the file.
SQL Server database transaction log file becomes corrupt due to several reasons, including unclean shutdown of the database, hardware fault, large-sized LDF file, and virus attack. A corrupt log file can make the database inaccessible.
You can try restoring the database from the last known good backup. But, keep in mind, restoring database from backup may cause data loss depending on the recovery strategy in place.
If the backup is not available or has failed, you can try rebuilding the corrupt log file using DBCC CHECKDB command. But this command has certain limitations. It may fail to fix a severely corrupted SQL Server database and returns an error. For instance, you may receive the following error when trying to repair a severely corrupt database file:
Also, running DBCC CHECKDB command with REPAIR_ALLOW_DATA_LOSS option results in data loss.
If none of the methods works, use Stellar Repair for SQL software to repair corrupt log file and fix SQL database issues, without data loss risk.
Q. I have been receiving an error ‘Transaction log backup’ failed. Can I use DBCC CHECKDB command to check for issues with transaction log backup?
Answer. No, DBCC CHECKDB does not check the transaction log file.
Q. Does taking a full backup reset the log backup?
Answer. No, doing a full backup does not reset the log backup.
Q. What is the difference between ‘DBCC CHECKDB command used with REPAIR_ALLOW_DATA_LOSS option’ and ‘ALTER DATABASE REBUILD LOG ON command’?
Answer: The DBCC CHECKDB command with REPAIR_ALLOW_DATA_LOSS option checks the database for any inconsistency error. The command first attempts to use the log file to recover from any database inconsistencies. Secondly, if the log is missing it helps rebuild the transaction log file.
The ALTER DATABASE REBUILD LOG command won’t work if there were open transactions (not written to disk) when the log file turns inaccessible.
Charanjeet is a Technical Content Writer at Stellar®who specializes in writing about databases, e-mail recovery, and e-mail migration solutions. She loves researching and developing content that helps database administrators, organizations and novices to fix multiple problems related to MS SQL and MySQL databases and Microsoft Exchange.
