Click here to Skip to main content
15,867,568 members
Articles / Database Development / SQL Server

How to Truncate Log File in SQL Server 2005

Rate me:
Please Sign up or sign in to vote.
4.39/5 (34 votes)
9 Jun 2006CPOL2 min read 576.4K   41   38
How to truncate log file in SQL Server 2005
Sample Image - shrink.jpg

Introduction

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 Microsoft 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:

  1. Detach the database
  2. Rename the log file
  3. Attach the database without the log file
  4. Delete the log file

Let’s say, the database name is testDev. In the SQL Server Management Studio,

  1. Highlight the database-> Tasks->Detach..-> Click OK
  2. Go to log file folder -> rename the testDev_log.ldf to be like testDev_log-aa.ldf
  3. 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
  4. After this is done, you can verify the contents of the attached database and then delete the log file

This way we can safely delete the log file and free up the space.

If you think this is very helpful, please leave your comments online. If you have any questions or suggestions, please email me at hong_wei_li@yahoo.com.

Happy SQLing!

History

  • 9th June, 2006: Initial post

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
Software Developer (Senior)
United States United States
C# and .NET Core
Typescript, node.js, jQuery, jQuery Mobile
Web Api and MVC
Angular and AngularJS
Azure
Databases (SQL Server and Oracle) and No-SQL Databases
Agile and Waterfall

Comments and Discussions

 
GeneralMy vote of 1 Pin
Jon Kehayias9-Sep-09 17:39
Jon Kehayias9-Sep-09 17:39 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.