Click here to Skip to main content
11,431,829 members (57,795 online)
Click here to Skip to main content

How to truncate log file in SQL Server 2008

, 24 Jul 2013 CPOL
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

 
GeneralIt is working fine Pin
Rajat Sahani8-Jan-15 0:42
memberRajat Sahani8-Jan-15 0:42 
GeneralWorked like a charm! Pin
protechnical24x717-Nov-14 22:52
memberprotechnical24x717-Nov-14 22:52 
GeneralIs this the only way to truncate? Pin
Mehul M Thakkar17-Feb-14 2:48
memberMehul M Thakkar17-Feb-14 2:48 
QuestionBackup is not created of log file Pin
Member 1023940223-Jan-14 23:27
memberMember 1023940223-Jan-14 23:27 
Generalvery useful Pin
SherryM28-Aug-13 10:06
memberSherryM28-Aug-13 10:06 
QuestionNice Post. Pin
Mas1124-Jul-13 19:31
memberMas1124-Jul-13 19: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 | Terms of Use | Mobile
Web01 | 2.8.150428.2 | Last Updated 24 Jul 2013
Article Copyright 2013 by Kumar Pankaj Verma
Everything else Copyright © CodeProject, 1999-2015
Layout: fixed | fluid