Set quoted_identifier off goset 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 - Minimum 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 (30), @ OriginalSize 'Pages / 8K or' Convert (VARCHAR (30), (@ 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 for new size. begin - outer loop. Select @counter = 0 while ((@counter <@originalsize / 16 ) - 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 loopdbcc shrinkfile (@LogicalFileName, @newsize)
SELECT DB_NAME () 'Log Last Size' Convert (VARCHAR (30), SIZE) 'PAGES / 8K or' Convert (Varchar (30), (Size * 8/1024)) 'MB' from sysfiles where Name = @LogicalFileName Drop Table DummyTrans Print '*** Database Log Compression Success ***' Set NoCount Off
Goset quoted_identifier off goset ANSI_NULLS ON Go
--USEDEXEC STRINK_LOGSPACE
