Shrink All Log Files

A quick maintenance task: this stored procedure walks every online database, finds the log file (any file whose name contains LOG), and shrinks it - first TRUNCATEONLY to release unlogged space, then down to zero. Use with care; shrinking logs repeatedly is usually a symptom rather than a cure.

CREATE PROCEDURE ShrinkAllLogFiles AS
BEGIN
	DECLARE @SQL VarChar(8000)
	DECLARE @DBN VarChar(1000)

	DECLARE Shrink CURSOR FAST_FORWARD FOR
		SELECT
			SysDB.name
		FROM
			sys.databases AS SysDB
		WHERE
			state_desc = 'ONLINE'
	OPEN Shrink

	FETCH FROM Shrink INTO @DBN

	WHILE(@@FETCH_STATUS = 0)
	BEGIN

		SET @SQL =
			'USE [' + @DBN + '];' +
			'' +
			'DECLARE @Log Varchar(500);' +
			'' +
			'SELECT @Log = RTrim(LTrim(SysF.[name]))' +
			' FROM sysfiles AS SysF' +
			' WHERE Upper(SysF.[name]) LIKE ''%LOG%'';' +
			'' +
			'DBCC SHRINKFILE (@Log, TRUNCATEONLY);' +
			'' +
			'DBCC SHRINKFILE (@Log, 0);'

		EXEC(@SQL)

		FETCH FROM Shrink INTO @DBN
	END

	CLOSE Shrink DEALLOCATE Shrink
END
GO