Script – Shrink log files of all databases

Although we strongly discourage shrinking files of SQL databases, but it can be an absolute necessity at times.

Below is a simple script you can use to shrink log files of all databases and quickly reclaim some space on the drive hosting log files (*.LDF).

DECLARE @dbname VARCHAR(256)
DECLARE @logname VARCHAR(256)

DECLARE @dbcursor CURSOR
SET @dbcursor = CURSOR
FOR
SELECT DB_Name(database_id), [name]
FROM sys.master_files
WHERE [type] = 1

OPEN @dbcursor

FETCH NEXT
FROM @dbcursor
INTO @dbname, @logname

WHILE @@FETCH_STATUS = 0
BEGIN
	--PRINT ('USE [' + @dbname + '] DBCC SHRINKFILE (''' + @logname + ''' , 0, TRUNCATEONLY)')		--Uncomment this to print the shrink commands
	EXEC ('USE [' + @dbname + '] DBCC SHRINKFILE (''' + @logname + ''' , 0, TRUNCATEONLY)')

	FETCH NEXT
	FROM @dbcursor
	INTO @dbname, @logname
END

CLOSE @dbcursor

DEALLOCATE @dbcursor