Click here to Skip to main content
15,861,172 members
Articles / Database Development
Tip/Trick

How to Shrink SQL Server database log files?

Rate me:
Please Sign up or sign in to vote.
4.23/5 (9 votes)
5 Jan 2011CPOL 38.7K   13   9
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.

Code


SQL
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)


Written By
Team Leader
India India
This member has not yet provided a Biography. Assume it's interesting and varied, and probably something to do with programming.

Comments and Discussions

 
GeneralReason for my vote of 1 Not a good advice Pin
msmits10-Jan-11 2:08
msmits10-Jan-11 2:08 
GeneralReason for my vote of 4 Good Tip....... Pin
Vivek Johari6-Jan-11 16:30
Vivek Johari6-Jan-11 16:30 
General@HLogic: Hi it's just a Tip/Trick so I just entered script o... Pin
thatraja4-Jan-11 14:52
professionalthatraja4-Jan-11 14:52 
GeneralReason for my vote of 1 Log files do not need to be truncate... Pin
HLogic4-Jan-11 14:20
HLogic4-Jan-11 14:20 
GeneralReason for my vote of 5 ok log Pin
ronal74-Jan-11 9:55
ronal74-Jan-11 9:55 
GeneralReason for my vote of 5 its good tip Pin
Pranay Rana2-Jan-11 23:32
professionalPranay Rana2-Jan-11 23:32 
GeneralYou shouldn't shrink whenever you do a full backup... Pin
msmits10-Jan-11 2:06
msmits10-Jan-11 2:06 
GeneralRe: You shouldn't shrink whenever you do a full backup... Pin
thatraja10-Jan-11 4:20
professionalthatraja10-Jan-11 4:20 
GeneralWelcome Pin
thatraja1-Feb-10 21:44
professionalthatraja1-Feb-10 21:44 

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.