缩小数据库日志的另类方法代码分析

2/9/2008来源:Oracle教程人气:5844


  SET NOCOUNT ON
  DECLARE @LOGICALFILENAME SYSNAME,
      @MAXMINUTES INT,
      @NEWSIZE INT
  USE abc---要操作的数据库
  SELECT @LOGICALFILENAME = 'abc_LOG',---日志文件名
      @MAXMINUTES = 10,---LIMIT ON TIME ALLOWED TO WRAP LOG.
      @NEWSIZE = 1---你想设定的日志文件大小(M)
  --SETUP /INITIALIZE
  DECLARE @ORIGINALSIZE INT
   SELECT @ORIGINALSIZE = SIZE
    FROM SYSFILES
   WHERE NAME = @LOGICALFILENAME
  
   SELECT 原日志大小='ORIGINAL SIZE OF ' + DB_NAME() + ' LOG IS ' +
      CONVERT(VARCHAR(30),@ORIGINALSIZE)+'8K PAGES OR '+
      CONVERT(VARCHAR(30),(@ORIGINALSIZE*8/1024))+'MB'
    FROM SYSFILES
   WHERE NAME = @LOGICALFILENAME
  
   CREATE TABLE DUMMYTRANS
         (DUMMYCOLUMN CHAR(8000) NOT NULL)
  
  DECLARE @COUNT INT,
      @STARTTIME DATETIME,
      @TRUNCLOG VARCHAR(255)
   SELECT @STARTTIME = GETDATE(),
      @TRUNCLOG = 'BACKUP LOG '+DB_NAME()+' WITH TRUNCATE_ONLY'
    DBCC SHRINKFILE (@LOGICALFILENAME,@NEWSIZE)
  EXEC(@TRUNCLOG)
  --WRAP THE LOG IF NECESSARY
  WHILE @MAXMINUTES >DATEDIFF(MI,@STARTTIME,GETDATE()) --TIME HAS NOE EXPIRED
   AND @ORIGINALSIZE =(SELECT SIZE FROM SYSFILES WHERE NAME = @LOGICALFILENAME)
   AND (@ORIGINALSIZE*8/1024)>@NEWSIZE
  BEGIN --OUTER LOOP.
    SELECT @COUNT = 0
      WHILE((@COUNT<@ORIGINALSIZE/16) AND (@COUNT<50000))
      BEGIN -- UPDATE
        INSERT DUMMYTRANS VALUES ('FILL LOG')
        DELETE DUMMYTRANS
        SELECT @COUNT = @COUNT + 1
      END
  EXEC (@TRUNCLOG)
  END
  SELECT 新日志大小='FINAL SIZE OF ' + db_NAME() +' LOG IS '+
  CONVERT(VARCHAR(30),SIZE)+'8K PAGES OR '+
  CONVERT(VARCHAR(30),(SIZE*8/1024))+'MB'
  FROM SYSFILES
  WHERE NAME = @LOGICALFILENAME
  DROP TABLE DUMMYTRANS
  SET NOCOUNT OFF