About a year ago, I wrote SQL SERVER – Shrinking Truncate Log File – Log Full. I was just going through some of the earlier posts and comments on this blog and one particular comment by Praveen Barath caught my eye. It is a very good explanation. I am copying complete explanation here with full credit to the original author.
How to truncate log file in SQL Server 2005
SQL Server 2005 is quite different from SQL Server 2000. To truncate log file is one thing which is different from SQL Server 2000. In SQL Server 2000, you just use Shrink to whatever file size you like. In SQL Server 2005, sometimes I cannot shrink the log file at all.
Here I want to describe some tricks to truncate log file for a database in SQL Server 2005. The work environment is MS SQL Server Management Studio.
I. Shrink the log file size at the right time
I found out this trick:
Immediately after I use the SSIS package or Import the data to the database ( highlight the database->Tasks->Import data … ), or Export the data from the database ( highlight the database->Tasks->Export data…), I can shrink the log file to the desired size, for example, 1MB. That is, highlight the database->Tasks->Shrink->Files
Set the file size, say, 1MB.
Then, click OK and you are done.
II. Eliminate the log file completely
Sometimes we just do not need the big log file. For example, I have a 40GB log file. I am sure I do not need this log file and want to get rid of it completely to free up the hard drive space. The logic is
a. Detach the database
b. Rename the log file
c. Attach the database without the log file
d. Delete the log file
Let’s say, the database name is test-drive. In the SQL Server Management Studio,
Highlight the database-> Tasks->Detach..-> Click OK
Go to log file folder -> rename the testDev_log.ldf to be like testDev_log-aa.ldf,
Highlight Databases->Attach…-> Click Add -> add the database testDev, highlight the log file and click the ‘Remove’ button. This means you only attach testDev.mdf
After this is done, you can verify the contents of the attached database and then delete the log file.
Reference: Pinal Dave (http://blog.SQLAuthority.com), Praveen Barath