/*
Shrink a named transaction log file belonging to a database
*/
SET NOCOUNT ON;

DECLARE @MaxMinutes INT, @NewSize INT, @Factor FLOAT;
/*
  The process has several control parameters, most of the time you only need to 
  worry about the first few as these are the big controls whereas the fifth is 
  simply a fine tuning control.

  Switch to the database we are attempting to shrink the logs for. Uncomment this to 
    automatically switch to a database, otherwise it uses the current db. */ --USE [MyDatabase];

-- Define the ideal size of logfile in MB and also how much time may be used to shrink the log SET @NewSize = 1; SET @MaxMinutes = 1;

/*
  Factor determining maximum number of pages to pad out based on the original 
  number of pages in use (single page = 8K).  Values in the range 1.0 - 0.8 seems to work 
  well for many databases.

  Increasing the number will increase the maximum number of pages allowed to be padded,
  which should force larger amounts of data to be dropped before the process finishes.  
  Often speeds up shrinking very large databases which are going through the process 
  before the timer runs out.

  Decreasing the number will decrease the maximum number of pages allowed to be padded,
  which should force less work to be done.  Often aids with forcing smaller databases 
  to shrink to minimum size when larger values were actually expanding them.

*/
SET @Factor = 0.95;                        

/*
  Automatically retrieve the logical filename of the largest logfile currently 
  assigned to the database.  If you want to manually select the file to shrink 
  then you can retrieve a list of the files using sp_helpfile and then setting 
  @LogicalFileName manually.
*/
DECLARE @LogicalFileName SYSNAME,
	@MaxSize INT,
	@OriginalSize INT,
	@StringData VARCHAR(500);

SELECT TOP 1
 @LogicalFileName = RTRIM( [name] ),
 @MaxSize = CASE [maxsize]
  WHEN -1 THEN -1
  ELSE ([maxsize] * 8)/1024
 END
FROM sysfiles
WHERE [groupid] = 0
ORDER BY [size] DESC;

/*
  All code after this point is driven by these parameters and will not require 
  editing unless you need to fix a bug in the padding/shrinking process itself. */

--Check user size is not larger than maximum file size
IF @MaxSize > 0 AND @NewSize > @MaxSize
BEGIN
  PRINT 'Note: '+CAST( @NewSize AS VARCHAR )+'MB exceeds the limit of '+CAST( @MaxSize AS VARCHAR )+'MB on this file, correcting...'
  PRINT ''
  SET @NewSize = @MaxSize;
END

SELECT @OriginalSize = size -- in 8K pages
FROM sysfiles
WHERE name = @LogicalFileName;

SELECT @StringData = '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;

PRINT @StringData;
PRINT '' --Drop the temporary table if it already exists
IF ( OBJECT_ID('[dbo].[DummyTrans]') IS NOT NULL )
  DROP TABLE [DummyTrans]

CREATE TABLE [DummyTrans]( [DummyColumn] CHAR(8000) NOT NULL );

-- Wrap log and truncate it.
DECLARE @Counter   INT,
        @MaxCount  INT,
        @StartTime DATETIME,
        @TruncLog  VARCHAR(500)

-- Try an initial shrink. (this is what causes data to be returned) DBCC SHRINKFILE (@LogicalFileName, @NewSize)

SET @TruncLog = 'BACKUP LOG [' + db_name() + '] WITH TRUNCATE_ONLY'; EXEC (@TruncLog)

-- Configure limiter
IF @OriginalSize / @Factor > 50000
    SET @MaxCount = 50000;
ELSE
    SET @MaxCount = @OriginalSize * @Factor;

-- Attempt to shrink down the log file
PRINT 'Minimum Quantity : '+CAST( @MaxCount AS VARCHAR(10) ); IF @MaxMinutes = 1
	PRINT 'Maximum Time : '+CAST( @MaxMinutes AS VARCHAR(10) )+' minute ('+CAST( @MaxMinutes*60 AS VARCHAR(10) )+' seconds)'; ELSE
	PRINT 'Maximum Time : '+CAST( @MaxMinutes AS VARCHAR(10) )+' minutes ('+CAST( @MaxMinutes*60 AS VARCHAR(10) )+' seconds)'; PRINT '';

SET @Counter = 0;
SET @StartTime = GETDATE();

--loop the padding code to reduce the log while
-- within time limit and 
-- log has not been shrunk enough
WHILE (
    (@MaxMinutes*60 > DATEDIFF(ss, @StartTime, GETDATE())) AND
    (@OriginalSize = (SELECT size FROM sysfiles WHERE name =
@LogicalFileName)) AND
    ((@OriginalSize * 8 / 1024) > @NewSize)
)
BEGIN --Outer loop.

    --pad out the logfile a page at a time while
    -- number of pages padded does not exceed our maximum page padding limit
    -- within time limit and 
    -- log has not been shrunk enough
    WHILE (
        (@Counter < @MaxCount) AND 
        (@MaxMinutes*60 > DATEDIFF(ss, @StartTime, GETDATE())) AND
        (@OriginalSize = (SELECT size FROM sysfiles WHERE name =
@LogicalFileName)) AND
        ((@OriginalSize * 8 / 1024) > @NewSize)
    )
    BEGIN --Inner loop
        
        INSERT INTO DummyTrans VALUES ('Fill Log')  -- Because it is a char field it inserts 8000 bytes.
        DELETE FROM DummyTrans

        SET @Counter = @Counter + 1

        --Every 1,000 cycles tell the user what is going on
        IF ROUND( @Counter , -3 ) = @Counter
        BEGIN
            PRINT 'Padded '+LTRIM( CAST( @Counter*8 AS VARCHAR(10) ) )+'K @ '+LTRIM( CAST( DATEDIFF( ss, @StartTime, GETDATE() ) AS VARCHAR(10) ) )+' seconds';
        END
    END

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

END
PRINT ''

SELECT @StringData = '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;

PRINT @StringData
PRINT ''

DROP TABLE DummyTrans;
PRINT '*** Perform a full database backup ***'

SET NOCOUNT OFF

/*
Based on:

 http://support.microsoft.com/support/kb/articles/q256/6/50.asp

Changes:
28.08.2001
Modified the inner loop so it tested the dx time so long overruns did not happen Modified the inner loop so it had a fixed minimum quantity so there was no skip in skip out

29.08.2001
Modified the inner loop so it had a dynamic minimum quantity to allow faster shrinkage

24.01.2002
Modified the USE statement so it uses brackets around the dbname Modified the @TruncLog variable so it uses brackets around the dbname

31.05.2002
Modified the code to use PRINT instead of SELECT in several cases Modified the code to use @MaxCount instead of two unclear rules 
Modified the code to use @Factor instead of several hard-coded values 
Commented the use of @Factor
Moved the configuration and @Counter init code to before the start of the first loop to avoid repetition Modified the code to display the process runtime in seconds rather than minutes

17.01.2003
Modified timing display code to handle 1 minute correctly

08.05.2003
Modified the instructions to make them easier to read & understand

*/