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 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 BEGIN EXEC(@TSQL) END ELSE BEGIN PRINT @TSQL END
Script messages after execution:
USE [MOC10987]; DBCC SHRINKFILE (N'MOC10987_log' , 512); USE [AdventureWorks]; DBCC SHRINKFILE (N'AdventureWorks_log' , 512);