Data/Database Compression - 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


ċ
Shrink DB log .sql
(3k)
Andy Hughes,
Jun 12, 2012, 9:22 AM
Comments