How To Shrink Database Log Files In SQL Server Using T-SQL

Hello!

Below are the scripts to shrink a specific log file assuming log files take up so much disk space.

-- View all log files in db and their log sizes
DBCC SQLPERF(LOGSPACE);

-- Backup log file first before shrinking
BACKUP LOG [SandboxDb]
TO DISK = 'Z:\SQL\Backups\Customer_DB\SandboxDb_Log.trn'
WITH INIT, COMPRESSION;
GO

-- View Log File name of DB
USE SandboxDb
GO
SELECT name FROM sys.master_files WHERE database_id = db_id() AND type = 1


/* Commands to shrink the db */
USE [SandboxDb];
GO

CHECKPOINT;
GO

-- Change the database recovery model to SIMPLE if FULL is the current recovery mode.
ALTER DATABASE SandboxDb
SET RECOVERY SIMPLE;
GO

-- Shrink log file to 10240 MB.
-- 10240 means 10,240 MB = 10 GB.
DBCC SHRINKFILE (SandboxDb_Log, 10240);
GO

-- Reset the database recovery model.
ALTER DATABASE SandboxDb
SET RECOVERY FULL;
GO

Cheers!