In some case, the Microsoft SQL Server Database Transaction Log (.LDF) file becomes very big even ton of Gigabytes. It’s wasting lot of disk space and causing some problems if you want to backup and restore the database.
To face with this issue, in this tutorial, you can apply either of following methods. I’m on Microsoft SQL Server 2008 but it also be applied for Microsoft SQL Server 2005.
1. Shrink the transaction log (.ldf) file
By using this method, you can shrink the log to the desired size, ex: 1MB
+ Right-click on the database you want to shrink => Tasks => Shrink => Files
Microsoft SQL Server 2008 Shrink
+ On Shrink File box, select Log on File type drop down list and select Reorganize pages before releasing unused space then set size for the log file, ex: 1 MB as picture below:
Microsoft SQL Server 2008 Shrink Set Size Log File
+ Click OK and you’re done.
The good point of this method is you can shrink at the right time but sometime, it doesn’t work especially with Microsoft SQL Server 2005. Let’s try the second method below in case it’s does not work.
2. Completely delete the log file
The main idea of this method is it will delete the big log file and create a new log file with the minimum size.
Follow steps below:
1. Detach the database
Right-click on the database => Tasks => Detach…
Microsoft SQL Server 2008 Detach
2. Delete the big log file
This step will completely erase it from your hard drive. However, from my suggestion you should back up the log file somewhere, ex: rename it from the file location (my case is: C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA)
Microsoft SQL Server 2008 Data Folder
3. Attach the database again
As we deleted the log file, so we do attach the database without the log file. Don’t worry, SQL Server will create a new log file for you which will be of the minimum size. That’s perfect!
+ Right-click Databases => Attach…
Microsoft SQL Server 2008 Attach
+ On Attach Database box, click Add…
Microsoft SQL Server 2008 Attach Add
+ Browser to the database (.mdf) file then click OK
Microsoft SQL Server 2008 Browser MDF
+ Select the log (.ldf) file then click Remove
Microsoft SQL Server 2008 Attach Remove Log
+ Finally, click OK and you’re done.
Note: after finished steps above, you should double check in your Data folder to see if there is a new log file created. If yes, let delete the big log file which was backup in step 2, remember?
