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

Tagged as

How to Shrink SQL Server database log files?

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


DECLARE @strDatabaseName nvarchar(255)
SET @strDatabaseName = 'YourDatabaseName' 
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?[^]


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


About the Author

Web Developer
India India
Programmer | 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 
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 
...unless your database is not growing anyways and you need the space.
One of the most costly database actions is shrinking/growing a data/log file.
Therefore you want to prevent this from happening automatically - especially on large production databases.
AutoShrink is therefore generally NOT a good option - you want your database to behave in a predictable manner.
The method most DBAs will recommend is to size your data/log files manually to a size that will stay constant, and then monitor the size and readjust when necessary.
Last, all depends on context - there is no one-size-fits-all solution.
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
Web02 | 2.8.140821.2 | Last Updated 5 Jan 2011
Article Copyright 2010 by thatraja
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid