Click here to Skip to main content
8,485,323 members and growing!
Email Password   Lost password?
Search within:



How to Shrink SQL Server database log files?

How to Shrink SQL Server database log files?
Sign Up to vote bad good
Add a reason or comment to your vote: x
Votes of 3 or less require a comment
See more: SQLdatabase
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?[^]
Posted 19 Jan '10
thatraja165K
Edited 5 Jan '11
Deeksha Shenoy71.4K
v8
Add an Alternative

Comments
Pranay Rana - 3 Jan '11
Reason for my vote of 5 its good tip
ronal7 - 4 Jan '11
Reason for my vote of 5 ok log
HLogic - 4 Jan '11
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.
thatraja - 4 Jan '11
@HLogic: Hi it's just a Tip/Trick so I just entered script only, otherwise(I mean it was article or blog) I would be explained other things well. Thank you.
Vivek Johari - 6 Jan '11
Reason for my vote of 4 Good Tip.......
msmits - 10 Jan '11
Reason for my vote of 1 Not a good advice

Sign Up to vote bad good
Add a reason or comment to your vote: x
Votes of 3 or less require a comment

Alternative 2

DUMP  TRANSACTION  FooDB  WITH  NO_LOG
BACKUP LOG FooDB WITH NO_LOG
DBCC SHRINKDATABASE(FooDB)
EXEC sp_dboption 'FooDB', 'autoshrink', 'TRUE'
  Permalink  
Posted 8 Feb '10
Huisheng Chen16.9K
Comments
Simon_Whale - 18 Oct '10
dump functionality has been removed with SQL Server 2008
Add an Alternative

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

add
  Print Discuss Answers RSS
Your Filters
Interested
Ignored
     
  1. Christian Graus (1,099)
  3. SAKryukov (601)
  4. CRDave1988 (432)
  1. SAKryukov (10,303)
  2. Christian Graus (7,108)
  3. OriginalGriff (5,156)
  4. Abhinav S (4,550)
  5. thatraja (4,371)

 
You must Sign In to use this message board. (secure sign-in)
 
Search this forum  
 FAQ
    Noise  Layout  Per page   
  RefreshFirst Prev Next
GeneralYou shouldn't shrink whenever you do a full backup... Pinmembermsmits3:06 10 Jan '11  
GeneralRe: You shouldn't shrink whenever you do a full backup... Pinmvpthatraja5:20 10 Jan '11  
GeneralWelcome Pinmemberthatraja22:44 1 Feb '10  
Last Visit: 18:22 13 Feb '12     Last Update: 18:22 13 Feb '12 1

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.5.120210.1 | Last Updated 5 Jan 2011
Copyright © CodeProject, 1999-2012
All Rights Reserved. Terms of Use
Layout: fixed | fluid