SQL2005日志清理

Dear 丶 2022-05-16 09:19 233阅读 0赞

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

1SELECT@LogicalFileName='Test\_log',

Test\_log 为日志逻辑名称。

\-- =============================================   
  \-- 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