Ryan Williams

DECLARE @database NVARCHAR(255)DECLARE @logFile NVARCHAR(255)SET @database = 'MyDataBase'SET @logFile = 'MyDataBase_log'-- Backup & Truncate transaction log.BACKUP LOG @database WITH TRUNCATE_ONLY GO-- Attempt to shrink transaction log.DBCC shrinkfile (@logFile, 1)GO-- Create a temporary table for fake transactions.CREATE TABLE ShrinkDataBaseTemp (MyField VARCHAR(10), PK INT )INSERT ShrinkDataBaseTemp (PK) VALUES (1)GO-- Perform 20'000 fake transactions to cause the transaction log to wrap.SET NOCOUNT ONDECLARE @Index INTSELECT @Index = 0WHILE (@Index < 20000)BEGIN   UPDATE ShrinkDataBaseTemp SET MyField = MyField WHERE PK = 1    SELECT @Index = @Index + 1ENDSET NOCOUNT OFFGO-- Delete the temporary table for fake transactions.DROP TABLE ShrinkDataBaseTempGO-- Backup & Truncate transaction log.BACKUP LOG @database WITH TRUNCATE_ONLY GO-- Shrink transaction log.DBCC shrinkfile (@logFile, 1)GO