SQL2005日志清理

SQL2005 1个多G的日志，有时候清不掉。所以找了这段代码。
有时候清理日志未清理成1M 请多执行几遍。一般清况下执行一次就可以的。
我碰到过要执行两遍的。

1SELECT@LogicalFileName='Test_log',

Test_log 为日志逻辑名称。

<!--<br/ /><br/ />Code highlighting produced by Actipro CodeHighlighter (freeware)<br/ />http://www.CodeHighlighter.com/<br/ /><br/ />-->


-- =============================================
-- Author:fighter
-- Createdate: 2009-3-15
-- Description: clearsqllog
-- =============================================
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 ' OriginalSizeof ' + db_name () + ' LOGis ' +
CONVERT ( VARCHAR ( 30 ), @OriginalSize ) + ' 8Kpagesor ' +
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 = ' BACKUPLOG ' + db_name () + ' WITHTRUNCATE_ONLY '

DBCC SHRINKFILE( @LogicalFileName , @NewSize )
EXEC ( @TruncLog )
-- Wrapthelogifnecessary.
WHILE @MaxMinutes > DATEDIFF (mi, @StartTime , GETDATE ()) -- timehasnotexpired
AND @OriginalSize = ( SELECT size FROM sysfiles WHERE name = @LogicalFileName )
AND ( @OriginalSize * 8 / 1024 ) > @NewSize
BEGIN -- Outerloop.
SELECT @Counter = 0
WHILE (( @Counter < @OriginalSize / 16 ) AND ( @Counter < 50000 ))
BEGIN -- update
INSERT DummyTrans VALUES ( ' FillLog ' )
DELETE DummyTrans
SELECT @Counter = @Counter + 1
END
EXEC ( @TruncLog )
END
SELECT ' FinalSizeof ' + db_name () + ' LOGis ' +
CONVERT ( VARCHAR ( 30 ),size) + ' 8Kpagesor ' +
CONVERT ( VARCHAR ( 30 ),(size * 8 / 1024 )) + ' MB '
FROM sysfiles
WHERE name = @LogicalFileName

DROP TABLE DummyTrans
SET NOCOUNT OFF

END