SET NOCOUNT ON
DECLARELogicalFileName sysname,
MaxMinutes INT.
NewSize INT

USE tablename — to operate the database name
SELECTLogicalFileName = ‘tablename_log’ — log file name
MaxMinutes = 10. — Limit on time allowed to wrap log.
NewSize = 1 — you want to set The size of the log file (M)

— Setup / initialize
Int DECLAREOriginalSize
Size = SELECTOriginalSize
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
CREATE TABLE DummyTrans
(DummyColumn char (800) not null)

DECLARECounter INT.
StartTime DATETIME.
TruncLog VARCHAR (255)
SELECTStartTime = GETDATE (),
TruncLog = ‘LOG BACKUP’ + db_name () + ‘ WITH TRUNCATE_ONLY ‘

DBCC SHRINKFILE (LogicalFileName,NewSize)
EXEC (TruncLog)
— Wrap the log if necessary.
WHILEMaxMinutes “DATEDIFF (mi. StartTime, GETDATE ()) — time has not expired
ANDOriginalSize = (SELECT size FROM sys WHERE name = filesLogicalFileName)
AND (/1024OriginalSize * “NewSize
BEGIN — Outer loop.
SELECTCounter = 0
WHILE ((Counter “OriginalSize / 16) AND (Counter “50,000))
BEGIN — update
INSERT DummyTrans VALUES ( ‘Fill Log’)
DELETE DummyTrans
SELECTCounterCounter + 1 =
END
EXEC (TruncLog)
END
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
SET NOCOUNT OFF