Problem: The actual size of the database is 600MB, the actual size of the log file is 33MB, but the log files occupy space for 2.8gb! tried a variety of ways, shirnk DATABASE, TRUNCATE log file, there is no way to reduce the files. Anyway, this should be a bug in SQL Server.
Workaround:
Then find the following code, you can reduce the log file to the size you want. Copy the code to the Query Analyzer, and then modify the 3 parameters (database name, log file name, and target log file size) and run it.
The following are the referenced contents:
-----
SET NOCOUNT on
DECLARE @LogicalFileName sysname,
@MaxMinutes INT,
@NewSize INT
use marias
--the database name to be manipulated
select @LogicalFileName = ' marias_log '
--log file name
@MaxMinutes = 10, & nbsp;
-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 |
