DBAtlas · Database operations atlas

Shrink the log file

SQL ServerBackup & Log

Command

-- First take a log backup so the log can be truncated (FULL model):
BACKUP LOG [Customer] TO DISK = N'D:\Backup\Customer_log_pre_shrink.trn' WITH COMPRESSION;

-- Find the logical name of the log file:
SELECT name, type_desc FROM sys.database_files WHERE type_desc = 'LOG';

-- Shrink (target 2048 MB):
USE [Customer];
DBCC SHRINKFILE (N'Customer_log', 2048);

What it does

Reduces the physical size of the transaction log file and returns space to the operating system.

When to use it

When the log has stayed permanently bloated after a one-off abnormal growth (large transaction/index rebuild).

Caution

DANGEROUS/CAUTION: Routine shrinking is BAD practice; performance drops while the log grows again and VLF fragmentation increases. Take a log backup first, otherwise the size will not drop. Shrink only an unnecessarily grown log file, not the data file. Then grow it back to an appropriate size in a single step.

Open in DBAtlas →

Related