Shrinking log file

Question

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

Answer 1

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.

Answer 2

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.