SQL: Guaranteed Way to Quickly Shrink the T-Log

My hero, Brad McGehee, has often advised against shrinking the transaction log when it's unnecessary. According to his (really amazing) SQL Server guidance, shrinking too often can lead to increased file fragmentation and excessive use of resources allocated to grow/shrink the file. See Brad's SQL Server articles for related best practices.

Sometimes, however, it is necessary. Often times, getting the transaction log to shrink can be tricky but here's how you can do it. That is, unless the t-log is currently in use (think SharePoint Search crawls), then the query will just wait there until the transactions are complete.

DBCC SHRINKFILE(logicalfilename, 1) BACKUP LOG MyDatabase WITH TRUNCATE_ONLY DBCC SHRINKFILE(logicalfilename, 1)   If you don't know the logical filename, run sp_helpfile for a list of database filenames. Be sure to make a FULL backup of your database once the shrink is complete.

Update (2025): The BACKUP LOG ... WITH TRUNCATE_ONLY approach was removed starting with SQL Server 2008 and is not supported in modern versions. If you truly need to reduce log size, use one of the supported patterns below depending on your recovery needs.

• If you need point-in-time recovery (FULL or BULK_LOGGED): take a log backup to truncate inactive VLFs, then shrink the log file to a reasonable size.

1-- Ensure database is in FULL or BULK_LOGGED recovery
2-- 1) Back up the log to mark inactive VLFs reusable
3BACKUP LOG [MyDatabase] TO DISK = N'X:\Backups\MyDatabase_log.trn' WITH INIT, NAME = N'Log backup before shrink';
4
5-- 2) Shrink the log file to a target size (in MB)
6-- Replace logical name with your log file logical name from sp_helpfile
7DBCC SHRINKFILE (N'MyDatabase_log', 2048); -- ~2 GB

• If you do not need point-in-time recovery (temporarily acceptable to switch): set recovery to SIMPLE, shrink, then set back to FULL and take a full backup to restart the log chain.

1-- 1) Temporarily switch to SIMPLE to allow truncation
2ALTER DATABASE [MyDatabase] SET RECOVERY SIMPLE;
3DBCC SHRINKFILE (N'MyDatabase_log', 1024); -- ~1 GB
4
5-- 2) Restore previous model (if needed) and re-establish the log chain
6ALTER DATABASE [MyDatabase] SET RECOVERY FULL;
7BACKUP DATABASE [MyDatabase] TO DISK = N'X:\Backups\MyDatabase_full.bak' WITH INIT, NAME = N'Full backup after recovery change';

General tips in 2025:

• Verify no long-running transactions, active replication, or availability groups are preventing truncation.
• Size the log to accommodate normal peak workloads to avoid repeated grow/shrink cycles.
• Use sys.databases, sys.dm_tran_database_transactions, and DBCC LOGINFO/DBCC LOGINFO (MyDatabase) equivalents (sys.dm_db_log_info in newer versions) to understand VLF layout and activity.

Update: Tibor Karaszi also has a great write-up on reasons to avoid shrinking the transaction log when possible and how to handle large log files.