Click here to Skip to main content
Click here to Skip to main content

How to truncate log file in SQL Server 2008

, 24 Jul 2013
Rate this:
Please Sign up or sign in to vote.
Truncating log file in SQL Server using T-SQL when log file is too large and database becomes unresponsive.

Background

In SQL Server data is stored using two physical files:  

  1. (.mdf)  extension  which contains the data. 
  2. (.ldf) extension which contains log. 

Log file size increases very rapidly and depend on the operation performed on the data. After a long time period this file becomes too large. When log file is too large it takes time to perform some operations like ( attach , de-attach, backup, restore ... etc ).  

Using the code

Step 1. Copy/type the below given SQL.

Step 2. Change @DBName to < Database Name>, @DBName_log to <Log File Name> 

Step 3. Execute the SQL. 

ALTER DATABASE @DBName SET RECOVERY SIMPLE WITH NO_WAIT
DBCC SHRINKFILE(@DBName_log, 1)
ALTER DATABASE @DBName SET RECOVERY FULL WITH NO_WAIT
GO  

License

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

Share

About the Author

Kumar Pankaj Verma
Technical Lead E. Soft Technologies
India India
Never try out to sort other's problem. Tell them the way only ........... Let do themselves.
Follow on   Google+

Comments and Discussions

 
GeneralIs this the only way to truncate? PinmemberMehul M Thakkar17-Feb-14 1:48 
QuestionBackup is not created of log file PinmemberMember 1023940223-Jan-14 22:27 
Generalvery useful PinmemberSherryM28-Aug-13 9:06 
QuestionNice Post. PinmemberMas1124-Jul-13 18:31 

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 | Mobile
Web03 | 2.8.140827.1 | Last Updated 24 Jul 2013
Article Copyright 2013 by Kumar Pankaj Verma
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid