Home > Handy Scripts > Shrink transaction log file(s)

Shrink transaction log file(s)

Sometimes there is an urgent need to shrink transaction log file/s of a single or multiple database/s which has/have grown to an extent that threatens to leave to free space on the logical drive, which would lead to entire database server operation freeze up.

The “quick-and-dirty” solution in such situation is to quickly shrink the t-log file/s. There are two methods of doing this. First one would simply attempt the file shrink operation. Second option is to back this/these file/s up first. Shrinking transaction log files without prior backing them up (first method) might or might not do the job – depending on what part of this/these file/s is marked “used”. If the tail of the transaction log file (latter VLFs) is marked as used than the first method wouldn’t work, i.e. the files won’t get shrank in size and won’t release the used space back to the operating system, so the problem would persist. To take care of that, the second method is more preferable, given the constraints of time and free space elsewhere to accommodate the t-log backup file/s. In addition, it’s more safe to backup transaction log file/s prior to shrinking them in case of production database/s running in full recovery mode – just in case of DRP point-in-time scenario.

First method:

–single database

DBCC SHRINKFILE([TLog_LogicalFileName], 1)

–generic script – all databases

sp_msforeachdb 'IF ''?'' NOT IN (''master'', ''tempdb'', ''tempdev'', ''model'', ''msdb'')
AND (SELECT recovery_model FROM master.sys.databases WHERE name = ''?'') = 1
AND (SELECT is_read_only FROM master.sys.databases WHERE name = ''?'') = 0
BEGIN
declare @LogFile nvarchar(2000)
USE [?]
SELECT @LogFile = name
FROM sys.database_files
WHERE type = 1
DBCC SHRINKFILE (@LogFile, truncateonly)
END'

Second method:

(try to stick to it, at least for all production databases as well as any databases running in full recovery mode)

–single database

USE [Database_Name]
GO
BACKUP LOG [Database_Name] TO DISK = N'[path]\[file_name].trn'
WITH NOFORMAT, INIT, NAME = N'[Database_Name] - Transaction Log  Backup', SKIP, NOREWIND, NOUNLOAD, [COMPRESSION*], COPY_ONLY, STATS = 1;
DBCC SHRINKFILE([TLog_LogicalFileName], 1)
GO

* [COMPRESSION] option is available in both Enterprise and Standard editions of SQL Server 2008R2, but only in Enterprise edition of SQL Server 2008

–generic, all databases

declare @SQL nvarchar(max);
declare @v_TLog_dir nvarchar(max);
declare @v_compression_YN nchar(1);
BEGIN
set @v_TLog_dir = 'C:\Temp';
set @v_compression_YN = 'Y';
select @SQL = coalesce(@SQL + char(13) + char(10),'') + N'
Use [' + d.name + '];' + CHAR(13) +
'BACKUP LOG ' + d.name + ' TO DISK = N''' + @v_TLog_dir + '\' + mf.name + '.trn'' WITH NOFORMAT, INIT, SKIP, NOREWIND, NOUNLOAD' +
case @v_compression_YN
when 'Y' then
', COMPRESSION, COPY_ONLY;'
when 'N' then
', COPY_ONLY;'
end + CHAR(13) +
'DBCC SHRINKFILE (' + quotename(mf.[name],'''') + ', 1) WITH NO_INFOMSGS;'
FROM sys.databases d INNER JOIN sys.master_files mf ON d.database_id = mf.database_id
WHERE d.name not in ('master', 'tempdb', 'model', 'msdb', 'ReportServer', 'ReportServerTempDB')
--AND d.recovery_model_desc = 'FULL'
AND d.is_read_only = 0
AND mf.type_desc = 'LOG'
ORDER BY d.name;
--print @SQL;
exec sp_executesql @SQL;
END;

062311_1657_SQLServerse2.png

NOTE: It’s highly recommended to comment out “exec sp_executesql @SQL;” and uncomment “–print @SQL;” lines respectively to first review the result. Then the lines could be swapped or, simply, the output copied over to new query window and ran there.

Advertisements
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: