常用的存儲過程-清除日志不管有多少個G也可清除

2019-11-17 03:50:49

字體：大中小

供稿：網(wǎng)友

來源于網(wǎng)上收集，整理使用。

SQL2005 1個多G的日志，有時候清不掉。所以找了這段代碼。
有時候清理日志未清理成1M 請多執(zhí)行幾遍。一般清況下執(zhí)行一次就可以的。
我碰到過要執(zhí)行兩遍的。

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

上一篇：Visual Studio集成開發(fā)環(huán)境無法啟動調(diào)試

下一篇：VS2010 Extension實踐