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 We Begin
Before discussing methods to fix an SQL database with a corrupt log file, it is important to determine the reasons that led to such an issue in the first place.
What Causes Corruption in a Transaction Log File?
Some possible reasons that may result in log file corruption are as follows:
- Abnormal System Shutdown: The system terminated abnormally, due to power outage or other reasons, without clean shutdown of the database.
- Storage Size Issue: Transaction log file has limited storage space, and exceeding its limit increases chances of corruption.
- Hardware Issue or Faulty Memory: Hardware fault occurred with the I/O subsystem used for hosting the system and database files, or faulty memory.
- Virus Infection: The system hosting SQL server database is affected by a virus attack, resulting in a corrupt log file.
Errors You May Encounter Due To Log File Corruption
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.
Methods to Repair SQL Server Database with a Corrupt Log File
Before applying any method, 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.
Method 1 ? Restore Database from a Good Backup
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:
- Open SQL Server Management Studio (SSMS), and connect to SQL Server.
- Expand Databases folder in Object Explorer.
- Right-click the Database, select Restore Database.
- In Restore Database window, under Source for restore section, select From device, and then click the button next to it to specify database file location.
- In Specify backup window, select Backup media type, and then click Add button to insert the backup file location.
- Select the backup file you need to restore and click OK.
- Again, click OK.
- The backup file will be listed on the Database restore window.
- Before restoring the backup, select Options under Select a page section in the Restore Database window.
- Select one of the checkboxes under Restore Options section. Click OK.
Method 2 ? Rebuild Corrupt Transaction Log File
Try rebuilding the corrupt log file to make the database accessible again. To do so, follow these steps:
- Put the database in EMERGENCY MODE by using the following command:
ALTER DATABASE SET EMERGENCY, SINGLE_USER
GORunning the above command will help bring the database up without a transaction log file.
- Once the database is up and set it to SINGLE_USER mode, run the DBCC CHECKDB command with REPAIR_ALLOW_DATA_LOSS option to repair the database with a corrupt 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')Method 3 ? Use Stellar Repair for MS SQL Software
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
- Download, install and launch Stellar Repair for MS SQL software.
- The Instruction window is displayed. Follow the instructions and click OK.
- In Select Database window, click Browse to select the corrupt MDF file you want to repair.
NOTE: If you are not aware of the MDF file location, click ?Search? to find and select the file.
- Once the file is selected, click Repair to initiate the repair process.
- The software provides a preview of the repaired database file in a tree-view list on the left panel of Preview window.
- Select all or specific database objects you want to recover, and then click Save on Home menu.
- In Save As window, select MDF under Save As section. Next, choose whether you want to save the repaired database file in ?New Database? or ?Live Database?.
- Fill in the required details under Connect to Server section, and then click Save.
Conclusion
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.
FAQ
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.
