Click here to Skip to main content
11,583,555 members (64,856 online)
Click here to Skip to main content

How to Truncate Log File in SQL Server 2005

, 9 Jun 2006 CPOL 496.9K 40
Rate this:
Please Sign up or sign in to vote.
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)

Share

About the Author

hong_wei_li@yahoo.com
Web Developer
United States United States
.Focus on database (SQL Server 2005/2000/7/6.5 and Oracle 10g) development with C#,

ASP.NET, ASP, Java, PHP.
.Like to work with MS Server 2005 SSIS and report service
.Like full cycle software design, development and deployment
.Microsoft Certified SQL Server Developer and DBA.

You may also be interested in...

Comments and Discussions

 
Questionانتا خارق Pin
Ahmed F Salameh9-Mar-14 23:43
memberAhmed F Salameh9-Mar-14 23:43 
General[My vote of 1] My vote of 1 Pin
muzzi_w7-Feb-13 13:35
membermuzzi_w7-Feb-13 13:35 
QuestionVery Helpful Pin
Jacky_kooboobird5-Jun-12 17:06
memberJacky_kooboobird5-Jun-12 17:06 
QuestionMy vote is 5 Pin
muhamd yusuf14-Feb-12 20:29
membermuhamd yusuf14-Feb-12 20:29 
GeneralMy vote of 5 Pin
Trong Thao3-Dec-10 17:42
memberTrong Thao3-Dec-10 17:42 
Generalshrinking log file in SQL2005 Pin
Okeola Mudashiru22-Oct-10 14:58
memberOkeola Mudashiru22-Oct-10 14:58 
GeneralMy vote of 1 Pin
blackr2d19-Apr-10 23:23
memberblackr2d19-Apr-10 23:23 
GeneralRe: My vote of 1 Pin
faisalcode4-Jun-10 11:52
memberfaisalcode4-Jun-10 11:52 
GeneralRe: My vote of 1 Pin
blackr2d5-Jul-10 22:49
memberblackr2d5-Jul-10 22:49 
GeneralRe: My vote of 1 Pin
shresthadk@hotmail.com4-Aug-10 16:29
membershresthadk@hotmail.com4-Aug-10 16:29 
QuestionHow to create Folder or File Name with Space ? Pin
Golden Jing22-Dec-09 0:08
memberGolden Jing22-Dec-09 0:08 
GeneralThis is not the way to shrink the file Pin
junk qwe23-Nov-09 12:34
memberjunk qwe23-Nov-09 12:34 
in a query window:

USE <DB_NAME>
BACKUP LOG <DB_NAME> WITH TRUNCATE_ONLY
DBCC SHRINKFILE(<LOG_NAME>, 1)


where <DB_NAME> is your database name
and <LOG_NAME> is the log name from sys.database_files table, name column (and not the physical filename !)
you can find your the name here:

SELECT * FROM sys.database_files
GeneralThanks Pin
Member 66136623-Oct-09 6:49
memberMember 66136623-Oct-09 6:49 
GeneralMy vote of 1 Pin
Jon Kehayias9-Sep-09 17:39
memberJon Kehayias9-Sep-09 17:39 
GeneralHORRIBLE ADVICE! IF YOU ARE A DBA OR ADMIN READ BELOW! Pin
Jon Kehayias9-Sep-09 17:32
memberJon Kehayias9-Sep-09 17:32 
GeneralVery helpful Pin
lingli2004-Sep-09 9:10
memberlingli2004-Sep-09 9:10 
GeneralRe: Very helpful Pin
Jon Kehayias9-Sep-09 17:36
memberJon Kehayias9-Sep-09 17:36 
GeneralRe: Very helpful Pin
Basel Nimer15-Mar-10 2:45
memberBasel Nimer15-Mar-10 2:45 
GeneralThank U. Pin
>FLY<</xml>14-Aug-09 0:42
member>FLY<</xml>14-Aug-09 0:42 
GeneralMy vote of 1 Pin
Howard Richards22-Jul-09 5:54
memberHoward Richards22-Jul-09 5:54 
GeneralThanks Pin
abii44427-May-09 8:09
memberabii44427-May-09 8:09 
GeneralDBCC SHRINKDATABASE Pin
Member 45889187-May-09 5:21
memberMember 45889187-May-09 5:21 
GeneralThanks Pin
ohnmartun25-Oct-08 5:25
memberohnmartun25-Oct-08 5:25 
GeneralDetaching and deleting the log is definitely not advisable Pin
David Portas8-Apr-08 12:18
memberDavid Portas8-Apr-08 12:18 
GeneralThanks to all contributers [modified] Pin
Super Coder!10-Sep-07 19:20
memberSuper Coder!10-Sep-07 19:20 
GeneralNice tip Pin
Vishal Halbe3-May-07 4:59
memberVishal Halbe3-May-07 4:59 
GeneralShrink log by statement Pin
dislektik30-Mar-07 3:10
memberdislektik30-Mar-07 3:10 
GeneralRe: Shrink log by statement Pin
Ravin200025-Feb-09 0:53
memberRavin200025-Feb-09 0:53 
GeneralGood tip - works for SQL Server 2000 as well Pin
Keith Worden4-Dec-06 3:55
memberKeith Worden4-Dec-06 3:55 
GeneralThe equivalent T-SQL statement Pin
Mr McGoo5-Sep-06 2:35
memberMr McGoo5-Sep-06 2:35 
GeneralRe: The equivalent T-SQL statement Pin
jlspublic2-May-07 4:33
memberjlspublic2-May-07 4:33 
GeneralRe: The equivalent T-SQL statement Pin
senglory12-Aug-10 14:58
membersenglory12-Aug-10 14:58 
GeneralDon't trash your transaction log... Pin
PaulRandal - MSFT17-Jun-06 1:52
memberPaulRandal - MSFT17-Jun-06 1:52 
GeneralRe: Don't trash your transaction log... Pin
iwonder10-Sep-06 16:24
memberiwonder10-Sep-06 16:24 
GeneralRe: Don't trash your transaction log... Pin
verena_techie14-Oct-08 12:25
memberverena_techie14-Oct-08 12:25 
Generalthanks Pin
amit.vaishnav15-Jun-06 22:37
memberamit.vaishnav15-Jun-06 22:37 
GeneralAlert: Need "Simple" recovery model Pin
datapvs13-Jun-06 4:10
memberdatapvs13-Jun-06 4:10 
GeneralFor the uninitiated Pin
Steve_Orbiz12-Jun-06 13:30
memberSteve_Orbiz12-Jun-06 13:30 

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

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

| Advertise | Privacy | Terms of Use | Mobile
Web04 | 2.8.150603.1 | Last Updated 9 Jun 2006
Article Copyright 2006 by hong_wei_li@yahoo.com
Everything else Copyright © CodeProject, 1999-2015
Layout: fixed | fluid