SQL Server - Shrink log for all user databases

Bulk shrink of transaction log for all databases. This script is handy when your transaction logs grown big on several databases. You want to shrink all log files quickly so you can script shrink scripts or run shrinks directly.

You can even use it in SQL Agent job step, but it is not good idea on production environment. When your developers are testing some new functionality causing growths of logs you can safely run it in your DEV or TEST environment.

By setting  variable @Execute on line 2 to 1 you will directly run shrink operation over all transaction logs with size over limit specified by variable @TargetSizeMB on line 3. Shrink operation is trying to make files size equal to that variable value (512 MB by default).

WARNING: It is not recommended to run this script with @Execute = 1 on production environments!

-- set up some variables first
DECLARE @Execute BIT = 0
DECLARE @TargetSizeMB INT = 512

-- and lets generate the script
	'USE [' + d.name + N']; DBCC SHRINKFILE (N''' + mf.name + N''' , ' + CAST(@TargetSizeMB AS VARCHAR) + ');' + CHAR(13) + CHAR(10) 
FROM  sys.master_files mf 
    JOIN sys.databases d ON mf.database_id = d.database_id 
WHERE d.database_id > 4
	AND d.user_access = 0
	AND mf.type = 1
	AND mf.size * 8 > @TargetSizeMB * 1024

-- print or execute your shrinking script, set @Execute = 1 to execute the script directly
IF @Execute = 1 


Script messages after execution:

USE [MOC10987]; DBCC SHRINKFILE (N'MOC10987_log' , 512);
USE [AdventureWorks]; DBCC SHRINKFILE (N'AdventureWorks_log' , 512);

Leave a Reply