Quick Fix – Shrink all log files

Note: This script truncates the transaction logs & shrinks the file. Do not use this if you are taking regular TLog backups as it will break the backup chain.

DECLARE @dbname VARCHAR(256)
DECLARE @dbcursor CURSOR
SET @dbcursor = CURSOR
FOR
SELECT [name]
FROM sys.databases
WHERE database_id > 4
OPEN @dbcursor
FETCH NEXT
FROM @dbcursor
INTO @dbname
WHILE @@FETCH_STATUS = 0
BEGIN
BEGIN TRY
  EXEC('BACKUP LOG [' + @dbname + '] TO DISK = ''NUL''')
  EXEC('USE [' + @dbname + '] CHECKPOINT')
  EXEC('USE [' + @dbname + '] DBCC SHRINKFILE (2, 0)')
  EXEC('USE [' + @dbname + '] CHECKPOINT')
  EXEC('BACKUP LOG [' + @dbname + '] TO DISK = ''NUL''')
  EXEC('USE [' + @dbname + '] DBCC SHRINKFILE (2, 0)')
END TRY
BEGIN CATCH
	EXEC('USE [' + @dbname + '] CHECKPOINT')
	EXEC('USE [' + @dbname + '] DBCC SHRINKFILE (2, 0)')
END CATCH
  FETCH NEXT
  FROM @dbcursor
  INTO @dbname
END
CLOSE @dbcursor
DEALLOCATE @dbcursor