Shrink DB Log File

Applicability:

SQL Server 2000: Tested

SQL Server 2005: Tested

SQL Server 2008: Tested

SQL Server 2008R2: Tested

SQL Server 2012: Not Tested

Credits:

Author: Unknown

Date: 2001

Description

This is a piece of code that I'm pretty sure was sourced from a Microsoft engineer on one of their forums. In any case, it has worked for me for over 10 years now with no modification.

Shrinking the database log file is ideally something that should never need to be done. However, even the best planned DB implementation is subject to changes in usage patterns or application bugs that can cause the transaction log to grow. Reporting and/or batch jobs are usually the culprits (as these are usually not something I find that is well planned or designed ahead of release time, despite the urgings of the DBA)

Therefore, the will inevitably come a time when the transaction log bloats and causes disk space or performance problems and this is where this script has been invaluable.

Just substitute the required values into the places noted (in Orange) and run the script.

Some points to note are:

- The script has a default max run time of 10 minutes. I have generally found that it will run in 2 or 3 minutes
- It may not work fully the first time (ie. only partial space recovery) and can safely be run multiple times
- It can be run (in an emergency) against an operational DB.

Ensure you have a full backup of the DB before running it.
Ensure you take a full backup of the DB after running it.

In all cases, ensure you are familiar with your backup scheme and that you know the effect that running this will have on the Log Sequence Number and your ability to restore to the level specified in any SLA, especially if your backup scheme includes Differential, Incremental or File level backups

Code

SET NOCOUNT ON

CREATE TABLE DummyTrans

(DummyColumn char (8000) not null)

DECLARE

@LogicalFileName SYSNAME,

@MaxMinutes INT,

@NewSize INT,

@OriginalSize INT

@Counter INT,

@StartTime DATETIME,

@TruncLog VARCHAR(255)

-- ****************************************************************

-- *** MAKE SURE TO CHANGE THE NEXT 4 LINES WITH YOUR CRITERIA. ***

-- ****************************************************************

-- This is the name of the database for which the log will be shrunk.

USE <DBName>

-- Use sp_helpfile to identify the log file name that you want to shrink.

SELECT

@LogicalFileName = '<LogFileName>',

@MaxMinutes = 10, -- Limit on time allowed to wrap log.

@NewSize = <DesiredLogSize> -- in MB

-- Setup / initialize

SELECT @OriginalSize = size -- in 8K pages

FROM sysfiles

WHERE name = @LogicalFileName

SELECT

'Original Size of '

+ DB_NAME()

+ ' LOG is '

+ CONVERT(VARCHAR(30),@OriginalSize)

+ ' 8K pages or '

+ CONVERT(VARCHAR(30),(@OriginalSize*8/1024))

+ 'MB'

FROM sysfiles

WHERE name = @LogicalFileName

-- Wrap log and truncate it.

SELECT @StartTime = GETDATE(),

@TruncLog =

'BACKUP LOG ['

+ DB_NAME()

+ '] WITH TRUNCATE_ONLY'

-- Try an initial shrink.

DBCC SHRINKFILE (@LogicalFileName, @NewSize)

EXEC (@TruncLog)

-- Wrap the log if necessary.

WHILE (@MaxMinutes > DATEDIFF (mi, @StartTime, GETDATE())) -- time has not expired

AND (@OriginalSize = (SELECT size FROM sysfiles WHERE name = @LogicalFileName)) -- the log has not shrunk

AND ((@OriginalSize * 8 /1024) > @NewSize) -- The value passed in for new size is smaller than the current size.

BEGIN -- Outer loop.

SELECT @Counter = 0

-- perform enough dummy transactions to align the log size with a page boundary

WHILE ((@Counter < @OriginalSize / 16) AND (@Counter < 50000))

BEGIN -- update

INSERT DummyTrans VALUES ('Fill Log') -- Because it is a char field it inserts 8000 bytes.

DELETE DummyTrans

SELECT @Counter = @Counter + 1

END -- update

EXEC (@TruncLog) -- See if a trunc of the log shrinks it.

END -- outer loop

SELECT

'Final Size of '

+ DB_NAME()

+ ' LOG is '

+ CONVERT(VARCHAR(30),size)

+ ' 8K pages or '

+ CONVERT(VARCHAR(30),(size*8/1024))

+ 'MB'

FROM sysfiles

WHERE name = @LogicalFileName

DROP TABLE DummyTrans

PRINT '*** Perform a full database backup ***'

SET NOCOUNT OFF