熱點推薦:
您现在的位置: 電腦知識網 >> 編程 >> SQL Server >> 正文

在SQL Server 2000查詢分析器裡面收縮數據庫日志

2013-11-15 14:36:30  來源: SQL Server 

  操作環境Windows Advanced Server 英文版 + spSQL Server 英文企業版+sp
  
  任務描述
  
  我有一個數據庫名稱為Cisi它的數據庫日志的名稱為Cisi_log
  這個日志的大小為M現在我要把它截斷令其收縮為M
  
  操作如下
  
  在查詢分析器裡面用sa登錄然後執行下列語句
  
   Prog: Xinsoft
   Time: :
  
  SET NOCOUNT ON
  DECLARE @LogicalFileName sysname
  @MaxMinutes INT
  @NewSize INT
  
  USE    Cisi          要操作的數據庫名
  SELECT @LogicalFileName = Cisi_log  日志文件名
  @MaxMinutes =            Limit on time allowed to wrap log
  @NewSize =               你想設定的日志文件的大小(M)
  
   Setup / initialize
  DECLARE @OriginalSize int
  SELECT @OriginalSize = size
  FROM sysfiles
  WHERE name = @LogicalFileName
  SELECT Original Size of + db_name() + LOG is +
  CONVERT(VARCHAR()@OriginalSize) + K pages or +
  CONVERT(VARCHAR()(@OriginalSize*/)) + MB
  FROM sysfiles
  WHERE name = @LogicalFileName
  Drop TABLE DummyTrans
  CREATE TABLE DummyTrans
  (DummyColumn char () not null)
  
  DECLARE @Counter  INT
  @StartTime DATETIME
  @TruncLog VARCHAR()
  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 not expired
  AND @OriginalSize = (SELECT size FROM sysfiles WHERE name =
  @LogicalFileName)
  AND (@OriginalSize * /) > @NewSize
  BEGIN Outer loop
  SELECT @Counter =
  WHILE ((@Counter < @OriginalSize / ) AND (@Counter < ))
  BEGIN update
  INSERT DummyTrans valueS (Fill Log)
  DELETE DummyTrans
  SELECT @Counter = @Counter +
  END
  EXEC (@TruncLog)
  END
  SELECT Final Size of + db_name() + LOG is +
  CONVERT(VARCHAR()size) + K pages or +
  CONVERT(VARCHAR()(size*/)) + MB
  FROM sysfiles
  WHERE name = @LogicalFileName
  DROP TABLE DummyTrans
  SET NOCOUNT OFF
  
  執行結果
  
  Original Size of Cisi LOG is K pages or MB
  Final Size of Cisi LOG is K pages or MB
  
  數據庫日志已成功地收縮為M
From:http://tw.wingwit.com/Article/program/SQLServer/201311/22054.html
    推薦文章
    Copyright © 2005-2013 電腦知識網 Computer Knowledge   All rights reserved.