SET NOCOUNT ON DECLARE @LogicalFileName sysname, @MaxMinutes INT, @NewSize INT
USE tablename -- 要操作的數據庫名 SELECT @LogicalFileName = 'tablename_log', -- 日志文件名 @MaxMinutes = 10, -- Limit on time allowed to wrap log. @NewSize = 1 -- 你想設定的日志文件的大小(M)
-- Setup / initialize DECLARE @OriginalSize int SELECT @OriginalSize = size FROM sysfiles WHERE name = @LogicalFileName SELECT 'Original Size of ' + db_name() + ' LOG is ' + CONVERT(VARCHAR(30),@OriginalSize) + ' 8K pages or ' + CONVERT(VARCHAR(30),(@OriginalSize*8/1024)) + 'MB' FROM sysfiles WHERE name = @LogicalFileName CREATE TABLE DummyTrans (DummyColumn char (8000) not null)
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 of ' + db_name() + ' LOG is ' + CONVERT(VARCHAR(30),size) + ' 8K pages or ' + CONVERT(VARCHAR(30),(size*8/1024)) + 'MB' FROM sysfiles WHERE name = @LogicalFileName DROP TABLE DummyTrans SET NOCOUNT OFF
The shrinking of log files is not immediate in SQL Server 7.0. The shrinking of log files does not occur until the active portion of the log moves. As updates are performed on the database, the shrink operation occurs at checkpoints or transaction log backups. Each log file is marked with the target_percent for the shrink operation. Each subsequent log backup or log truncation attempts to shrink the file to bring its size as close to the target_percent as possible. Because a log file can be shrunk only to a virtual log file boundary, it may not be possible to shrink a log file to a size smaller than the size of a virtual log file even if it is not being used. Please refer to SQL Book Online for the details.
RESOLUTION
Below script will help to shrink the log file immediately, pls keep it running for 3~4 minutes and then stop it manually.
\* Run "select fileid, name,filename from ..sysfiles" to get the fileid which you want to shrink *\
use go dbcc shrinkfile(fileid,notruncate) dbcc shrinkfile(fileid,truncateonly) create table t1 (char1 char(4000)) go declare @i int select @i = 0 while (1 = 1) begin while (@i 100) begin insert into t1 values ('a') select @i = @i +1 end truncate table t1 backup log with truncate_only end go
