Summary: There can be several reasons that can cause corruption in a transaction log file (LDF). But the most common reason that can corrupt a log file is detaching and rebuilding the log file to reduce its size. Read on to know how you can reduce the size of the log file while preventing it from getting corrupted. If the log file is corrupt and the backup is not available, try using Stellar Repair for MS SQL software. The software repairs the SQL database MDF file. Once the file is repaired, you can create an LDF file from the repaired MDF file.
A common problem is to have the log file corrupt. This can be corrupt for many reasons:
In this article we will show what to do in case that your log file is corrupt and cannot connect to the database.
The current article applies to SQL Server in any version, you will also need the SSMS which can be downloaded here:
The log file is corrupted for many reasons. When you disconnect and try to reconnect with the detach and attach features, your log file can be corrupted.
It is a bad practice to detach and rebuild the log file to reduce the size. Instead of that, you can shrink the file and use the recovery models according to your needs. First we will talk about the log files and then we will talk about recovery models and shrinking.
The transaction logs file are very important files that belongs to a SQL Server database. These logs store the database transactions and the database changes. It can be used to restore the database information. However, if it is corrupt, can corrupt the entire database. A common problem is that the transaction log file grows a lot and the users want to reduce the space used. Here you have some common advices to reduce the size in order to avoid a wrong manipulation:
Recovery model
You can reduce the information in the transaction logs, otherwise the space used by the transaction logs can be very high compared with the database files. You can control what information can be logged using the recovery model property.
Change the recovery model to reduce the information logged. The following options are available:
In order to modify the recovery model, you can use the Transaction SQL (T-SQL) or SSMS.
The following T-SQL code shows how to change the recovery model to full:
ALTER DATABASE Your-database SET RECOVERY FULL;
If you want to change to SIMPLE OR Bulk logged, use these commands:
Simple recovery:
ALTER DATABASE Your-database SET RECOVERY SIMPLE;
Bulk Logged:
ALTER DATABASE Your-database SET RECOVERY BULK_LOGGED;
You can also modify the recovery model in SSMS. To do that, right click the database and in Database Properties:
Go to the Options page and select the Recovery model of your preference:
Compress the transaction log file
Another way to shrink your transaction log file is to shrink your log file. The shrink operation will shrink your data. You can shrink the mdf file or log files.
In order to shrink a log file, use the following command:
DBCC SHRINKFILE (Yourlogfile_log, 1)
You may need to set to simple recovery model or backup the transaction file before shrinking.
Another way to shrink the log file is to use the SSMS.
In SSMS, go to Tasks>Shrink>Files
In File type, select the Log file. Make sure to backup the transaction log first.
It is a bad practice to detach the database and try to attach rebuilding the log and remove the previous log file to reduce the space. This can be a problem and sometimes there are errors to rebuild the log file.
It is recommended to shrink the file or use the simple mode to reduce the space used. It is not really a good practice to use the simple recovery model, but you can temporary used in some scenarios.
If you do not need to recover data on a specific date and time using transaction log backups, a simple recovery model can be a good option.
If the log cannot connect to the database, it may be possible that your MDF file is corrupt. In order to fix the MDF file corruption, you can use the SQL Recovery Software. Download the software from here:
If you select MDF the database will be restored and repaired.
Conclusion
The transaction log files store the SQL Server transactions of the databases. They can grow a lot and sometimes when we try to modify the size of the logs, the databases get corrupted.
Using the recovery model options can help you to control the size of the transaction log. For example, you can use the bulk-logged mode if you are doing a bulk copy of data.
Also, you can shrink the file to reduce the size. Detaching files, renaming them and rebuilding the log should be used in emergencies and not to reduce the size as a routine. Make sure to backup the logs (if your database is not in simple mode) and if your database is corrupt, you can always use Stellar Repair for MS SQL.
You may also be interested in How to Attach SQL database without LDF
Priyanka is a technology expert working for key technology domains that revolve around Data Recovery and related software's. She got expertise on related subjects like SQL Database, Access Database, QuickBooks, and Microsoft Excel. Loves to write on different technology and data recovery subjects on regular basis. Technology freak who always found exploring neo-tech subjects, when not writing, research is something that keeps her going in life.
hello,
Before reading this blog, We had lost our hope with severely damaged MDF file. But, this software seems our last hope. Definitely, we will try this software asap.