3

Some of my SQL Server's log files (ldf) are huge, like 5GB. I Found an article about shrinking, but am not sure why the author repeated DBCCSHRINKFILE 6 times.

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

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
3

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.
  • 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
  • Switching your "recovery model to simple" is should only be done if you fully understand the implications. – James Jenkins Nov 8 '18 at 16:15
0

This answer assumes your databases are in Full recovery, if using Simple recovery, there are different considerations.

First, understand the business reason your databases are using full recovery. The related post Is it ok to change from full recovery to simple recovery in Sql Server has some considerations.

Next, learn how often t-log (.ndf) backups are being taken on the instance and where they are backed up to (there is little value in taking backups and leaving them on the server)

Before attempting to shrink the t-log (.ndf), you need to run a t-log backup. Then run the shrink one time, then if it didn't shrink as much as you want run another t-log backup and try the shrink again Related

There are also considerations for Virtula Log Files (VLF) but that is a lesson for another day.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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