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
DECLARE @TSQL VARCHAR(MAX) = ''

-- and lets generate the script
SELECT @TSQL = @TSQL +
	'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 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 
BEGIN
	EXEC(@TSQL)
END
ELSE 
BEGIN
	PRINT @TSQL
END

Results

Script messages after execution:

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

Leave a Reply

Your email address will not be published. Required fields are marked *