Below is a script that shrinks SQL Server transaction logs tested on SQL Server 2008R2.
Before running the script, you should ensure that you take a transaction log backup of your databases (which obviously means you should have already taken a full backup). The transaction log backup will free the virtual logs from within the transaction log file.
The script simply tries to shrink the transaction log file by 25% for any databases that are not called “MASTER”, “MODEL”, “MSDB” or “TEMPDB”.
If you wish to shrink logs by more than 25%, either change the script, or run it multiple times until it can’t shrink the logs any further.
NOTE: When executing the script in SQL Management Studio, you should set the results output to “Text”, so that you can see the output of the script.
USE master
GO
DECLARE @database_id int,
@database_name nvarchar(128),
@file_id int,
@file_name nvarchar(128),
@size_mb int,
@new_size int;
DECLARE @dbcc_output char(5);
DECLARE Cur_LogFiles CURSOR LOCAL FOR
SELECT database_id,
UPPER(DB_NAME(database_id)) database_name,
file_id,
name file_name,
(size*8)/1024 size_mb
FROM master.sys.master_files
WHERE type_desc = 'LOG'
AND state_desc = 'ONLINE'
AND UPPER(DB_NAME(database_id)) NOT IN ('MASTER','TEMPDB','MSDB','MODEL')
ORDER BY size_mb DESC;
BEGIN
OPEN Cur_LogFiles
FETCH NEXT FROM Cur_LogFiles
INTO @database_id, @database_name, @file_id, @file_name, @size_mb
WHILE @@FETCH_STATUS = 0
BEGIN
-- Determine 25% of our current logfile size.
SET @new_size = @size_mb*0.75;
-- Set context to the database that owns the file and shrink the file with DBCC.
PRINT 'Database: ' + @database_name + ', file: ' + @file_name + ', size: ' + CONVERT(varchar,@size_mb,25) + 'mb';
PRINT 'Attempting to shrink file: ' + @file_name + ' by 25% to: '+ CONVERT(varchar,@new_size,25) + 'mb';
EXEC ('USE [' + @database_name + ']; DBCC SHRINKFILE (['+@file_name+'],'+@new_size+');');
FETCH NEXT FROM Cur_LogFiles
INTO @database_id, @database_name, @file_id, @file_name, @size_mb
END;
CLOSE Cur_LogFiles
DEALLOCATE Cur_LogFiles
END;
