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

How to Shrink SQL Server database log files?

By , 5 Jan 2011
Rate this:
Please Sign up or sign in to vote.
Database Logs should be truncated whenever you perform a full backup of the database. You can also manually truncate them as follows.
 

Code

DECLARE @strDatabaseName nvarchar(255)
SET @strDatabaseName = 'YourDatabaseName' 
BACKUP LOG @strDatabaseName WITH TRUNCATE_ONLY 
DBCC SHRINKDATABASE (@strDatabaseName, 0)--Parameters are Database Name & Target %
It was tested in SQL Server 2005 & it's working. It was also tested in SQL 2008 & it's working too.
 

For Your Information

Why won't my transaction log shrink?[^]

License

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

About the Author

thatraja
Web Developer
India India
Programmer | CodeProject MVP, Mentor | Author | Geek | Blogger | Bachelor | Dude
 
About him & his blog? check out here. Currently he's updating the site, he'll return soon there with more things*.
 
In Codeproject, he posted a blogpost & more than couple of Tip/Tricks, hope he'll write more soon.
 
Favorite techs are MVC, WCF, Mobile, HTML5, CSS3, jQuery, etc.,
 
His hobbies are watching movies, CP, being alone, etc., But now he's working on something*.
 
* - SCAMP???
Follow on   Twitter   Google+   LinkedIn

Comments and Discussions

 
GeneralReason for my vote of 1 Not a good advice Pinmembermsmits10-Jan-11 2:08 
GeneralReason for my vote of 4 Good Tip....... PinmemberVivek Johari6-Jan-11 16:30 
General@HLogic: Hi it's just a Tip/Trick so I just entered script o... Pinmvpthatraja4-Jan-11 14:52 
GeneralReason for my vote of 1 Log files do not need to be truncate... PinmemberHLogic4-Jan-11 14:20 
Reason for my vote of 1
Log files do not need to be truncated after full backups nor should shrinkdatabase be used in this context as it will lead to fragmentation and high VLF counts.
GeneralReason for my vote of 5 ok log Pinmemberronal74-Jan-11 9:55 
GeneralReason for my vote of 5 its good tip PinmemberPranay Rana2-Jan-11 23:32 
GeneralYou shouldn't shrink whenever you do a full backup... Pinmembermsmits10-Jan-11 2:06 
GeneralRe: You shouldn't shrink whenever you do a full backup... Pinmvpthatraja10-Jan-11 4:20 
GeneralWelcome Pinmemberthatraja1-Feb-10 21:44 

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
Web04 | 2.8.140415.2 | Last Updated 5 Jan 2011
Article Copyright 2010 by thatraja
Everything else Copyright © CodeProject, 1999-2014
Terms of Use
Layout: fixed | fluid