To narrow SQL Server log file SQL statements

server| statement
Paste the following code into SQL Query Analyzer (Note that the 3 parameters of the Red section are modified):

SET NOCOUNT ondeclare @LogicalFileName sysname, @MaxMinutes int, @NewSize int

Use Dicky-database name Select @LogicalFileName = ' Dicky_log ' that requires the log to be reduced--the logical name of the log file, @maxminutes =, non-physical file name--Limit on time Allowe D to wrap log. @NewSize = 2-the size of the log file that needs to be reset (in M), this value must be smaller than the original file size

--Setup/initializedeclare @OriginalSize intselect @OriginalSize = size from Sysfileswhere name = @LogicalFileNameSELECT ' Original Size of ' + db_name () + ' LOG is ' + CONVERT (VARCHAR, @OriginalSize) + ' 8K pages or ' + CONVERT (VARCHAR (30) , (@OriginalSize *8/1024)) + ' MB ' from sysfileswhere name = @LogicalFileNameCREATE TABLE Dummytrans (dummycolumn char (8000) NOT null)

DECLARE @Counter INT, @StartTime DATETIME, @TruncLog VARCHAR (255) SELECT @StartTime = GETDATE (), @TruncLog = ' BACKUP LOG ' + D B_name () + ' with Truncate_only '

DBCC shrinkfile (@LogicalFileName, @NewSize) EXEC (@TruncLog)-Wrap the log if necessary. While @MaxMinutes > DATEDIFF (MI, @StartTime, GETDATE ())--time has not expiredand @OriginalSize = (SELECT size from S Ysfiles WHERE name = @LogicalFileName) and (@OriginalSize * 8/1024) > @NewSize BEGIN--Outer loop. SELECT @Counter = 0WHILE ((@Counter < @OriginalSize/16) and (@Counter < 50000)) BEGIN--Updateinsert Dummytrans VA Lues (' Fill Log ') DELETE dummytransselect @Counter = @Counter + 1END EXEC (@TruncLog) End SELECT ' Final Size of ' + Db_nam E () + ' LOG is ' +convert (VARCHAR, size) + ' 8K pages or ' + CONVERT (VARCHAR), (size*8/1024) + ' MB ' from Sysfiles whe RE name = @LogicalFileNameDROP TABLE dummytransset NOCOUNT off