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
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 !!