Shrink Log File Script

This script from Eli Leiba
will attempt to shrink the log file to a specific size.

Create PROCEDURE Sp_ShrinkLog 
              (@LogF  varchar(200),   @NSize INT) 
AS
--BY ELI L.
-- DATE 08/2002
-- DESC: attempt to shrink the log file to a specific size
--    gets logical file name and attempt size

SET NOCOUNT ON
DECLARE @MaxMin INT, @LogicalFileName sysname,@origs int
DECLARE @Cnt INT,@StartTime DATETIME, @Truncl VARCHAR(255)

SELECT @LogicalFileName = @LogF
SELECT @MaxMin = 2

SELECT @origs = size 
 FROM sysfiles
 WHERE name = @LogicalFileName

SELECT 'Original Size OF ' + db_name() + ' LOG IS ' + 
CONVERT(VARCHAR(30),@origs) + ' 8K pages or ' + 
CONVERT(VARCHAR(30),(@origs*8/1024)) + 'MB'
 FROM sysfiles
 WHERE name = @LogicalFileName

CREATE TABLE DumpTrn  (DumCol char (8000) NOT null)

SELECT @StartTime = GETDATE(),
                @Truncl = 'BACKUP LOG ['+ db_name() + '] WITH TRUNCATE_ONLY'

DBCC SHRINKFILE (@LogicalFileName, @NSize)
EXEC (@Truncl)

WHILE @MaxMin > DATEDIFF (mi, @StartTime, GETDATE()) 
 AND @origs =    (SELECT size FROM sysfiles WHERE name = @LogicalFileName) 
 AND (@origs * 8 /1024) > @NSize 
                 
  BEGIN 

    set @Cnt = 0

    WHILE ((@Cnt < @origs / 16) AND (@Cnt < 50000))

       BEGIN -- UPDATE
        INSERT DumpTrn VALUES ('Fill Log') -- Because it IS a char field it inserts 8000 bytes.
        DELETE DumpTrn
        set  @Cnt = @Cnt + 1
     END-- UPDATE
    EXEC (@Truncl) -- 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 DumpTrn

PRINT '*** Perform a FULL DATABASE BACKUP ***'

SET NOCOUNT OFF

-- Example How to Use
use northwind
go
exec sp_shrinkLog 'Northwind_Log',2,2

»


See All Articles by Columnist Eli Leiba