About log compression

Compressed


SET quoted_identifier off


Go


SET ansi_nulls off


Go








CREATE PROCEDURE strink_logspace


as


SET NOCOUNT on


DECLARE @LogicalFileName sysname,


@MaxMinutes INT,


@NewSize INT





SELECT @LogicalFileName = RTrim (name),


@MaxMinutes = 10--Maximum execution time


@NewSize = 10--min space


from Sysfiles where status & 0x40 = 0x40





--Setup/initialize


DECLARE @OriginalSize int


SELECT @OriginalSize = size-in 8K pages


from Sysfiles


WHERE name = @LogicalFileName





SELECT db_name () + ' Log original size ' +


CONVERT (VARCHAR, @OriginalSize) + ' pages/8k or ' +


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


from Sysfiles


WHERE name = @LogicalFileName





CREATE TABLE Dummytrans


(Dummycolumn char (8000) NOT NULL)





--Wrap log and truncate it.


DECLARE @Counter INT,


@StartTime DATETIME,


@TruncLog VARCHAR (255)


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 to new size is smaller than the current size.


BEGIN--Outer loop.


SELECT @Counter = 0


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)--The trunc of the ' log shrinks it.


End--Outer loop





DBCC shrinkfile (@LogicalFileName, @NewSize)





SELECT db_name () + ' Log last size ' +


CONVERT (VARCHAR, size) + ' pages/8k or ' +


CONVERT (VARCHAR, (size*8/1024)) + ' MB '


from Sysfiles


WHERE name = @LogicalFileName


DROP TABLE Dummytrans


PRINT ' * * * Database log Compression Successful * * *


SET NOCOUNT off





Go


SET quoted_identifier off


Go


SET ansi_nulls on


Go





--used


exec strink_logspace