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

Related Posts

1 Reply to “Quick Fix – Shrink all log files”

  1. Wonderful script !!! This script truncated all the transactional logs in one go which was 2 TB in my case.

    Great job Dapinder !! Thanks a lot !!

Leave a Reply

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

SQL Shots with Dapinder Arora | SQL Server Solutions