HiveBrain v1.2.0
Get Started
← Back to all entries
patternsqlMinor

Shrinking log file

Submitted by: @import:stackexchange-dba··
0
Viewed 0 times
shrinkingfilelog

Problem

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

Solution

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

  • Swith the recovery model to simple.



  • 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.



  • Switch back to the original recovery model.

Context

StackExchange Database Administrators Q#11565, answer score: 3

Revisions (0)

No revisions yet.