Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Some of the sql log files (idf) are hugh like 5gb. Found a article (http://extended64.com/blogs/rhoffman/archive/2009/06/11/shrink-sql-server-2008-database-log-file-script.aspx) but not sure why he repeated dbcc shrinkfile 6 times.

Can someone please confirm and suggest a clean script to do this? Thanks

 Use DatabaseName 
 GO
Alter Database DatabaseName Set Recovery Simple
GO
    Alter Database DatabaseName Set Recovery Full
    GO
    DBCC SHRINKFILE ('LogFileName', 1)
    GO
    DBCC SHRINKFILE ('LogFileName', 1)
   GO
   DBCC SHRINKFILE ('LogFileName', 1)
  GO
   DBCC SHRINKFILE ('LogFileName', 1)
   GO
   DBCC SHRINKFILE ('LogFileName', 1)
   GO
   DBCC SHRINKFILE ('LogFileName', 1)
   GO
share|improve this question
    
Check this link to see, how to resolve this :- sql-blogs.com/2012/01/07/… –  Harsh Chawla Jan 25 '12 at 15:43

1 Answer 1

up vote 4 down vote accepted

I don't see any reason to repeat the command 6 times as you said. 1 within the command is the target size. DBCC ShrinkFile allows to specify a value < the original size when the file was created. Beware though of the line : Alter Database DatabaseName Set Recovery Simple You will have to backup your database first as if you use another recovery model (either full or bulk_logged) the log chain will be broken.

  1. Backup your database
  2. Swith the recovery model to simple.
  3. Run DBCC ShrinkFile command (I recommend a reasonable size here , if you have a 5Gb log file you may need a bigger original size, and or bigger increments in MB for its growth.
  4. Switch back to the original recovery model.
share|improve this answer
2  
You almost never want to shrink your log file to 1MB - auto-growth is the devil. More likely, you want to truncate it, and then alter your DB to set it to a reasonable size, so that you have a reasonable number of VLFs (at reasonable sizes). See bit.ly/7Oy3qq for more info, specifically point #8 at the bottom of the article as well as the linked articles). –  Brandon Jan 25 '12 at 17:15

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.