Teach you how to shrink SQL Server log files

A few days ago also encountered a large log file problem, the actual size of the database is 600M, the actual size of the log file is 33M, but the log files occupy a space of 2.8G!!!

Try a variety of ways, Shirnk DATABASE, TRUNCATE LOG file, there is no way to reduce the file. Anyway, this should be a bug in SQL Server.

Then find the following code, you can reduce the log file to the size you want. Copy the code into the Query Analyzer, and then modify the 3 parameters (database name, log file name, and target log file size) and run it (I've used it many times)

-----

SET NOCOUNT on

DECLARE @LogicalFileName sysname,

@MaxMinutes INT,

@NewSize INT

Use Marias-the name of the database to manipulate

SELECT @LogicalFileName = ' Marias_log ',--log file name

@MaxMinutes =--Limit on time allowed to wrap log.

@NewSize = 100-the size of the log file you want to set (M)

--Setup/initialize

DECLARE @OriginalSize int

SELECT @OriginalSize = size

From Sysfiles

WHERE name = @LogicalFileName

SELECT ' Original Size of ' + db_name () + ' LOG is ' +

CONVERT (VARCHAR, @OriginalSize) + ' 8K pages or ' +

CONVERT (VARCHAR (@OriginalSize *8/1024)) + ' MB '

From Sysfiles

WHERE name = @LogicalFileName

CREATE TABLE Dummytrans

(Dummycolumn char (8000) NOT NULL)

DECLARE @Counter INT,

@StartTime DATETIME,

@TruncLog VARCHAR (255)

SELECT @StartTime = GETDATE (),

@TruncLog = ' BACKUP LOG ' + db_name () + ' with Truncate_only '

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)

and (@OriginalSize * 8/1024) > @NewSize

BEGIN--Outer loop.

SELECT @Counter = 0

while (@Counter < @OriginalSize/16) and (@Counter <50000))

BEGIN--Update

INSERT Dummytrans VALUES (' Fill Log ')

DELETE Dummytrans

SELECT @Counter = @Counter + 1

End

EXEC (@TruncLog)

End

SELECT ' Final Size ' + db_name () + ' LOG is ' +

CONVERT (VARCHAR (), size) + ' 8K pages or ' +

CONVERT (VARCHAR (), (size*8/1024)) + ' MB '

From Sysfiles

WHERE name = @LogicalFileName

DROP TABLE Dummytrans

SET NOCOUNT off