来源于网上收集,整理使用。
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
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