ASHOKA SAMRAT: Convert your LOG file smaller

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…