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