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.
Introduction
A common problem is to have the log file corrupt. This can be corrupt for many reasons:
- A virus
- A hacker attack
- A power shut down or supply problem
- A user malicious manipulation of the file
- A user accidental manipulation
- Human mistake or ignorance
In this article we will show what to do in case that your log file is corrupt and cannot connect to the database.
Requirements
The current article applies to SQL Server in any version, you will also need the SSMS which can be downloaded here:
- SQL Server installer
- SSMS installer
Getting started
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 log files
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:
- Full, that logs all the information with no exceptions.
- Bulk logged is used when the bulk operations are applied. When bulk operations are made, the information logged is minimal.
- Simple is used for minimal logged information. You cannot do log backups with this mode.
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.
Common errors
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.
What to do if the MDF file is corrupt
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:
- The software will require the MDF file:
- Once selected the MDF file you can press the repair button.
- The software will show a view of all the SQL Server objects:
- When you save the information, you can save in an MDF file in SQL Server, in Excel, CSV, HTML.
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
