Shrinking Transaction Logs

Shrinking SQL Server transaction logs is a common task for DBAs, especially when logs grow unexpectedly large. Here’s a step-by-step guide to help you manage and shrink them safely:

If the log is full, start with Transaction Log Full. Shrinking does not fix a full log.

Important Considerations

  • Don’t shrink logs regularly—only do it when necessary (e.g., after a large transaction or maintenance).
  • Set appropriate auto growth settings to avoid frequent growth/shrink cycles.
  • Don’t switch to SIMPLE recovery without approval. It breaks the log backup chain and needs a full backup afterwards (see Transaction Log Full).

USE [Liberate]

GO
DBCC SHRINKFILE (N'LibLibrary_log' , 0, TRUNCATEONLY)
GO

If TRUNCATEONLY doesn’t release enough space, shrink to a target size in MB that covers normal peak use:

USE [Liberate]
GO
DBCC SHRINKFILE (N'LibLibrary_log', 1024)   -- target 1 GB; never shrink a log to 1 MB
GO

From my SQL Server DBA handbook.