Click here to Skip to main content
15,743,427 members
Articles / Database Development / SQL Server / SQL Server 2008
Tip/Trick
Posted 24 Jul 2013

Stats

99.8K views
3 bookmarked

How to truncate log file in SQL Server 2008

Rate me:
Please Sign up or sign in to vote.
4.78/5 (11 votes)
24 Jul 2013CPOL
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. 

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)


Written By
Technical Lead E. Soft Technologies
India India
Never try out to sort other's problem. Tell them the way only ........... Let do themselves.

Comments and Discussions

 
GeneralMy vote of 5 Pin
Krishna Kishore K2-Sep-15 18:36
Krishna Kishore K2-Sep-15 18:36 
GeneralIt is working fine Pin
Rajat Sahani7-Jan-15 23:42
Rajat Sahani7-Jan-15 23:42 
GeneralWorked like a charm! Pin
protechnical24x717-Nov-14 21:52
protechnical24x717-Nov-14 21:52 
GeneralIs this the only way to truncate? Pin
Mehul M Thakkar17-Feb-14 1:48
Mehul M Thakkar17-Feb-14 1:48 
QuestionBackup is not created of log file Pin
Member 1023940223-Jan-14 22:27
professionalMember 1023940223-Jan-14 22:27 
Generalvery useful Pin
SherryM28-Aug-13 9:06
SherryM28-Aug-13 9:06 
QuestionNice Post. Pin
Mas1124-Jul-13 18:31
Mas1124-Jul-13 18:31 

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.