Summary: A SQL database log file might be corrupted due to the large size of transaction log files, accidental deletion of data, etc. A corrupt log file may render the database inaccessible or mark it ‘In Recovery’ or ‘Suspect’ mode. This blog discusses the solutions to repair and rebuild a SQL database with a corrupted or deleted log file.
Contents
Overview of the Issue
Every SQL database comprises of three file types viz. Main or Primary Database files (.mdf), Secondary database files (.ndf) and Transactional Log files (.ldf). While the main database files and secondary files are used to store database objects like tables, views, indexes, etc., the log files (.ldf) store the transactional logs and changes made by every transaction. The SQL server engine reads the complete log files and performs the recovery process when the SQL Server is started. However, if the reading or recovery process fails, the database remains offline, and it is marked ?Suspect? or ?Recovery Pending? as per the stage at which failure occurred. This article will discuss the probable causes for corrupt log files, and then we will discuss the solutions to rebuild the SQL database.
Reasons for Corrupt Transactional Log Files
The transaction log files are record files of the SQL database and are critical for recovery. Several reasons may render the log files in the SQL database corrupt. These reasons include:
- Unexpected termination or forced system shutdown.
- Malware or virus attacks on the system, causing file damage or making the database inaccessible.
- Increased size of log files resulting in lack of free space and exceeded configured maximum file size.
- Configuration issues with the I/O subsystem used to host the system and user database files.
- Changes in the SQL Server Account.
- Accidental data deletion.
Ways to Rebuild SQL Database With Corrupt or Deleted Log Files
Before we start with any method, it is important to check the SQL Server error logs and events logs and if you come across an error caused due to hardware issues, you need to get it repaired and see if the problem is resolved. If there is no hardware issue, start with the methods below:
Method 1 – Recover Database from an Existing Backup
To recover a database from an existing backup follow the steps described below:
Step 1: Launch the ?SQL Server Management Studio? (SSMS).
Step 2: Right-click on the Databases and select ?Restore Database?.
Step 3: In the ?Source? section on ?General? page, specify the source and location.
Step 4: Select the database to be restored from the drop-down list.
Note: If you can?t find your intended database, it means the backup for it has not been created, and the current method can?t be used to repair your database.
Step 5: Click the [?] button to open the ?Select Backup Devices? box and choose the ?Backup Media Type? from the drop-down list.
Step 6: Click ?Add? to open a dialogue box describing what you can select to restore.
Step 7: Once you are done with selecting the options in ?Backup Media? list, click ?OK? to return to the ?General? page.
Step 8: Select the database name you want to restore from the list box.
Step 9: Leave the ?Restore to? option set at ?To the last backup taken? or select a backup of your choice from the ?Timeline? and click ?OK?.
Step 10: Select the backups to restore from the ?Backup Sets to Restore? grid and click ?OK?.
Method 2 – Rebuild Damaged or Corrupted Transaction Log file
To rebuild a corrupt or damaged SQL Server log file, you need to put the database in Emergency mode. First, make sure that your data has backup and follow the steps below:
Step 1: Use the below command to set your database in ?Emergency Mode?.
USE master
GO
ALTER DATABASE [Database_name] SET EMERGENCY
GO
ALTER DATABASE [Database_name] SET SINGLE_USER
GOStep 2: Now that the database is set to Single User mode, execute the DBCC CHECKDB command with REPAIR_ALLOW_DATA_LOSS option.
DBCC CHECKDB ([DATABASE_NAME], REPAIR_ALLOW_DATA_LOSS) WITH NO_INFOMSGS, ALL ERRORMSGS;
GOCaution! It is important to know that you might lose data while executing the DBCC CHECKDB command with REPAIR_ALLOW_DATA_LOSS option.
Note: If the corruption still exists, use the following query to rebuild the log file. Before running this query, put the database to Offline Mode and change the name of the corrupt log file.
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 this method seems a bit difficult and you wish to adopt an easier and effective method then follow the next method.
Method 3 – Repair the SQL Database Using Stellar Repair for MS SQL
Use a SQL Repair tool like Stellar Repair for MS SQL to restore the database without any complexities. The software uses advanced algorithms to repair corrupt MDF and NDF files. Once repaired, a new transaction log file (.ldf) is created. Follow the steps below to repair and rebuild your SQL log file:
Step 1: Download the Stellar Repair for MS SQL.
Step 2: Once download is complete, run the software. This opens a ?Select Database? window with options to browse or find the corrupt MDF file.
Step 3: Once you have located the file click the ?Repair? tab at the bottom right corner of the window to start the repairing process.
Step 4: A preview of the repaired database and its components get displayed.
Step 5: After verifying the data, click ?Save?.
Step 6: To proceed with the repaired database saving process, choose any of these options:
- New Database – This option saves the data in a new database with ?Recovered? prefixed to the database name.
- Live Database – This option saves the repaired database in an existing database.
- Other Formats – Using this option, you can save the repaired data in file formats like CSV, HTML, or XLS.
Step 7: Enter details to connect to your SQL Server instance, select the location to save the repaired file, then click ‘Next‘.
Step 8: Choose any of these saving modes:
- Fast Saving – Use this mode for saving a large-sized repaired database. This option saves the data first and indexes later for faster database restore.
- Standard Saving – If you need to save the data and indexes together, select the ‘Standard Saving’ mode.
Step 9: Once the database is saved, ?File saved successfully? message appears.
To Conclude
It is pertinent to highlight that there are very scarce methods to repair SQL Server databases. Furthermore, even those available methods may be quite hectic to follow. Therefore, it is easier and more convenient to follow the next option and repair your SQL database using Stellar Repair for MS SQL. It provides a simplified yet effective user interface and recovers all database components such as schemas, triggers, tables, keys, etc., with utmost accuracy. Moreover, once you have your main data files and secondary data files repaired, you will have access to the corrupt or deleted transaction log files (.ldf) as well.
FAQs
Q. How do I restore a .mdf file only?
A. You can restore .mdf files without restoring the .ldf files by using either the SQL Server Management Studio or T-SQL. Try to attach the .mdf files using the SSMS and once the .mdf file is attached successfully the SQL server will create a .ldf file.
Q. How to check database corruption in SQL Server?
A. You need to run the DBCC CHECKDB command that will verify your database file. It will present the location of problematic areas and suggest the data repair requirements. For infected pages, run the DBCC PAGE command.
Q. Transaction log for database is full, what does it mean?
A. Factors that may cause filling of transaction log for the database include a long-running transaction or a paused database mirroring session. Refer to the Microsoft Troubleshooting Guide to troubleshoot the issue.
Q. Can we shrink the MSDB log file?
A. Yes. Although this is not a frequently used operation for any database. Generally, you can resolve the issue of very large MSDB log files by running the clean-up history task. If it does not work, then check what is using the space and then shrink the MSDB log file using the command below
— SHRINK THE MSDB LOG FILE
USE MSDB
GO
DBCC SHRINKFILE(MSDBLog, 512)
GO
— SHRINK THE MSDB Data File
USE MSDB
GO
DBCC SHRINKFILE(MSDBData, 1024)
GO
Rebuild the indexes once you are done shrinking the MSDB log files.
Q. What is the size limit of the SQL database log file?
The SQL Log file initial size of a newly created database is 8MB, with the auto-growth amount set at 64MB by default. It must be understood that there is no single optimal value for Log File initial size and auto-growth that fits all circumstances. But, as per the best practices, setting the initial size of the SQL Database Log file to 20-30% of the database data file size and the auto-growth to a suitable amount, ranging more than 1024MB, is good for a normal workload.
