常用的存储过程-清除日志 不管有多少个G也可清除 (转)

来源于网上收集，整理使用。

 

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 

 

1

0

0

(请您对文章做出评价)

« 上一篇： [转] [精华] SMTP协议原始命令码和工作原理

posted on 2010-01-26 23:32 hsj2010 阅读(622) 评论(10)   编辑 收藏 网摘

FeedBack:

1755100

#1楼

2010-01-27 07:49 | oec2003       

我记得好像一条sql语句就可以吧，不知道和您的方法有什么不同
   回复   引用   查看     

#2楼

2010-01-27 07:56 | 徐少侠       

DBCC SHRINKFILE (@LogicalFileName, @NewSize)
   回复   引用   查看     

#3楼

2010-01-27 07:56 | 徐少侠       

@TruncLog = 'BACKUP LOG ' + db_name() + ' WITH TRUNCATE_ONLY'

   回复   引用   查看     

#4楼

2010-01-27 08:00 | 徐少侠       

我记得是要先
backup log
然后dbcc shrinkfile

所以楼主有时候要执行两遍


   回复   引用   查看     

#5楼

2010-01-27 08:00 | 徐少侠       

backup log 的参数可以使用with no_log
   回复   引用   查看     

#6楼

2010-01-27 08:01 | 徐少侠       

newsize用也可以
除非要收缩到指定大小

当然,0也不可能真的到0,好像也是1M
   回复   引用   查看     

#7楼

2010-01-27 08:12 | OK_008       

原理是一样的：

先
DBCC SHRINKFILE (@LogicalFileName, @NewSize)

再是
BACKUP LOG dbname WITH TRUNCATE_ONLY



或者，先设置数据库为简单恢复模式，再收缩文件，然后再设置数据库为完整恢复模式。（要是数据库原来就是简单恢复模式，就直接收缩文件即可）
e.g:

USE AdventureWorks;
GO
-- Truncate the log by changing the database recovery model to SIMPLE.
ALTER DATABASE AdventureWorks
SET RECOVERY SIMPLE;
GO
-- Shrink the truncated log file to 1 MB.
DBCC SHRINKFILE (AdventureWorks_Log, 1);
GO
-- Reset the database recovery model.
ALTER DATABASE AdventureWorks
SET RECOVERY FULL;
GO
要是截断日志，可以这样：
BACKUP LOG dbname WITH NO_LOG
Dbcc Shrinkdatabase('dbname')



   回复   引用   查看     

#8楼

2010-01-27 09:11 | Ben       

BACKUP LOG <DBNAME> WITH NO_LOG
   回复   引用   查看     

#9楼

2010-01-27 09:15 | 尘尘       

路过
   回复   引用   查看     

#10楼

2010-01-27 09:25 | 徐少侠       

引用

OK_008：
原理是一样的：

先
DBCC SHRINKFILE (@LogicalFileName, @NewSize)

再是
BACKUP LOG dbname WITH TRUNCATE_ONLY