SQL2005 1个多G的日志,有时候清不掉。所以找了这段代码。
有时候清理日志未清理成1M 请多执行几遍。一般清况下执行一次就可以的。 我碰到过要执行两遍的。 1 SELECT @LogicalFileName = 'Test_log', Test_log 为日志逻辑名称。 4 5 6 -- ============================================= 7 -- Author: fighter 8 -- Create date: 2009-3-15 9 -- Description: clear sql log 10 -- ============================================= 11 CREATE PROCEDURE [dbo].[tools_ClearSqlLog] 12 13 AS 14 BEGIN 15 16 SET NOCOUNT ON 17 DECLARE @LogicalFileName sysname, 18 @MaxMinutes INT, 19 @NewSize INT 20 21 SELECT @LogicalFileName = 'Test_log', 22 @MaxMinutes = 1, 23 @NewSize = 1 24 25 -- Setup / initialize 26 DECLARE @OriginalSize int 27 SELECT @OriginalSize = size FROM sysfiles WHERE name = @LogicalFileName 28 SELECT 'Original Size of ' + db_name() + ' LOG is ' + 29 CONVERT(VARCHAR(30),@OriginalSize) + ' 8K pages or ' + 30 CONVERT(VARCHAR(30),(@OriginalSize*8/1024)) + 'MB' 31 FROM sysfiles 32 WHERE name = @LogicalFileName 33 34 CREATE TABLE DummyTrans 35 (DummyColumn char (8000) not null) 36 37 38 DECLARE @Counter INT, 39 @StartTime DATETIME, 40 @TruncLog VARCHAR(255) 41 SELECT @StartTime = GETDATE(), 42 @TruncLog = 'BACKUP LOG ' + db_name() + ' WITH TRUNCATE_ONLY' 43 44 DBCC SHRINKFILE (@LogicalFileName, @NewSize) 45 EXEC (@TruncLog) 46 -- Wrap the log if necessary. 47 WHILE @MaxMinutes > DATEDIFF (mi, @StartTime, GETDATE()) -- time has not expired 48 AND @OriginalSize = (SELECT size FROM sysfiles WHERE name = @LogicalFileName) 49 AND (@OriginalSize * 8 /1024) > @NewSize 50 BEGIN -- Outer loop. 51 SELECT @Counter = 0 52 WHILE ((@Counter < @OriginalSize / 16) AND (@Counter < 50000)) 53 BEGIN -- update 54 INSERT DummyTrans VALUES ('Fill Log') 55 DELETE DummyTrans 56 SELECT @Counter = @Counter + 1 57 END 58 EXEC (@TruncLog) 59 END 60 SELECT 'Final Size of ' + db_name() + ' LOG is ' + 61 CONVERT(VARCHAR(30),size) + ' 8K pages or ' + 62 CONVERT(VARCHAR(30),(size*8/1024)) + 'MB' 63 FROM sysfiles 64 WHERE name = @LogicalFileName 65 66 DROP TABLE DummyTrans 67 SET NOCOUNT OFF 68 69 END 70 71 |
|