SQL2005 1个多G的日志,有时候清不掉。所以找了这段代码。
有时候清理日志未清理成1M 请多执行几遍。一般清况下执行一次就可以的。
我碰到过要执行两遍的。
1 SELECT @LogicalFileName = 'Test_log',
Test_log 为日志逻辑名称。
-- =============================================
-- Author: fighter
-- Create date: 2009-3-15
-- Description: clear sql log
-- =============================================
CREATE PROCEDURE [ dbo ] . [ tools_ClearSqlLog ]
AS
BEGIN
SET NOCOUNT ON
DECLARE @LogicalFileName sysname,
@MaxMinutes INT ,
@NewSize INT
SELECT @LogicalFileName = ' Test_log ' ,
@MaxMinutes = 1 ,
@NewSize = 1
-- 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 )
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 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
END