Modify the 3 parameters (database name, log file name, and target log file size) to run
SETNOCOUNT on DECLARE @LogicalFileNamesysname,@MaxMinutes INT, @NewSize INT UseTableName--the name of the database to manipulateSELECT @LogicalFileName = 'Tablename_log',--log file name@MaxMinutes = Ten,--Limit on time allowed to wrap log.@NewSize = 1 --The size of the log file you want to set (M)--setup/initializeDECLARE @OriginalSize int SELECT @OriginalSize =size fromSysfilesWHEREName= @LogicalFileName SELECT 'Original Size of' + db_name()+ 'LOG is' + CONVERT(VARCHAR( -),@OriginalSize)+ '8K pages or' + CONVERT(VARCHAR( -),(@OriginalSize*8/1024x768))+ 'MB' fromSysfilesWHEREName= @LogicalFileName CREATE TABLEDummytrans (DummycolumnChar(8000) not NULL) DECLARE @Counter INT, @StartTime DATETIME, @TruncLog VARCHAR(255) SELECT @StartTime = GETDATE(), @TruncLog = 'BACKUP LOG' + db_name()+ 'With truncate_only' DBCCShrinkfile (@LogicalFileName,@NewSize) EXEC(@TruncLog) --Wrap the log if necessary. while @MaxMinutes > DATEDIFF(MI,@StartTime,GETDATE())--Time has not expired and @OriginalSize =(SELECTSize fromSysfilesWHEREName= @LogicalFileName) and(@OriginalSize * 8 /1024x768)> @NewSize BEGIN --Outer Loop.SELECT @Counter = 0 while((@Counter < @OriginalSize / -) and(@Counter < 50000)) BEGIN --UpdateINSERTDummytransVALUES('Fill Log') DELETEDummytransSELECT @Counter = @Counter + 1 END EXEC(@TruncLog) END SELECT 'Final Size of' + db_name()+ 'LOG is' + CONVERT(VARCHAR( -), size)+ '8K pages or' + CONVERT(VARCHAR( -), (size*8/1024x768))+ 'MB' fromSysfilesWHEREName= @LogicalFileName DROP TABLEDummytransSETNOCOUNTOFF
Clear SQL Server database log files using statements