INF: 如何將 SQL Server 交易記錄檔壓縮

• 在 Microsoft SQL Server 7.0 裡，SHRINKFILE 及 SHRINKDATABASE 命令設定了壓縮的目標大小。 每一個記錄檔案都由這些命令所標示，但是實際上壓縮檔案的卻是記錄檔備份或記錄檔截斷。 所以，在使用 SHRINKFILE 或 SHRINKDATABASE 命令後，還必須在記錄檔有機會壓縮前，發出命令來截斷記錄檔。
• 您無法將記錄檔的大小壓縮成小於這些條件允許的大小：
  
  
  • 若要將記錄檔壓縮為小於原始大小，就必須使用 DBCC SHRINKFILE 來壓縮個別的檔案。 您無法使用 DBCC SHRINKDATABASE 來將記錄檔壓縮為小於其原始或要求的自訂大小。 其原始大小的定義是由 CREATE DATABASE 加上任何明確的 ALTER DATABASE 命令所產生的記錄檔大小。 此原始大小並不包括記錄檔的自動成長。
    
    
  • 實體記錄檔案絕不能小於目前記錄檔案所使用的空間總數。您可以使用 DBCC SQLPERF (LOGSPACE) 命令來監視使用的空間總數。
    
    
  • 目前Model資料庫記錄檔的大小，為該伺服器上任何資料庫記錄檔中最小的。根據預設值，Model資料庫記錄檔會小於 1 MB。
    
    
  • 因為記錄檔只可能在虛擬記錄檔案 (VLF) 界限裡被壓縮，即使空間未被使用，也不可能將記錄檔案壓縮成小於 VLF 的大小。 同樣地，如果正在使用部份的 VLF，您將無法壓縮 VLF 的任何空間。如需更多詳細資訊，請參閱《SQL Server 線上叢書》中的 「虛擬記錄檔案」及 「交易記錄檔實體結構」主題。
  
  
  
• 交易記錄檔為一「換行」的記錄檔。這表示在任何時間裡，無論在記錄檔的開始、中間及/或結尾， VLF 均擁有「可使用」或「可重新使用」的空間。若要將記錄檔壓縮，則在記錄檔的結尾必須有「可使用」的空間，而 不只是在記錄檔中任何其他地方有可用空間。此外，您也只可以壓縮整個 VLF。若要將交易記錄檔壓縮，VLF 記錄檔案的結尾必須為非使用中及被截斷的狀態。 如需更多詳細資訊，請參閱《SQL Server 線上叢書》中的「截斷交易記錄檔」主題。

• 在做了會影響系統的變更前後，一定要執行系統資料庫及使用者資料庫的備份。 DBCC SHRINKFILE 及 DBCC SHRINKDATABASE 是非記錄式的作業，且執行它們會使進一步的交易記錄檔備份無效。 在您執行 DBCC SHRINKFILE 或 DBCC SHRINKDATABASE 兩者之一的命令後，一定要製作一個完整的資料庫備份。
  
  
• 請確認在應該發生壓縮的期間，沒有排定進行備份。
  
  
• 請確認沒有舊有、長期執行，或未複製的交易。若要執行這項作業，請使用類似下列的程式碼：

  DBCC OPENTRAN (database_name)

• 執行 DBCC SHRINKFILE 或 DBCC SHRINKDATABASE 命令以標示壓縮點。DBCC SHRINKFILE 及 DBCC SHRINKDATABASE 的權限預設為 sysadmin 固定伺服器角色或 db_owner 固定資料庫角色的成員，並且不能轉移。如需有關這些命令之間差異的詳細資訊， 請參閱《SQL 線上叢書》中的下列主題 (請注意不同的參數)：
  
  

  DBCC SHRINKFILE     (file_name, target_size)
  DBCC SHRINKDATABASE (database_name, target_percent)

• 建立一些虛擬的交易，使記錄檔換行，然後再使用 BACKUP 命令來截斷記錄檔。 實際上將記錄檔壓縮成所標示目標大小記錄檔的是 BACKUP 陳述式。
  
  下列範例說明如何建立虛擬交易，使記錄檔換行成單一邏輯記錄檔並使其截斷以進行壓縮。 請依您的環境需要來修改範例。

  SET NOCOUNT ON
  DECLARE @LogicalFileName sysname,
          @MaxMinutes INT,
          @NewSize INT
  
  -- Your criteria here.
  USE     Your_Database_Name              -- This is the name of the database 
  for which the log will be shrunk.
  SELECT  @LogicalFileName = 'Your_log',  -- Use sp_helpfile to identify the logical file name that you want to shrink.
          @MaxMinutes = 10,               -- Limit on time allowed to wrap log.
          @NewSize = 100                  -- in MB
  
  -- Setup / initialize
  DECLARE @OriginalSize int
  SELECT @OriginalSize = size -- in 8K pages
    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)
  
  
  -- Wrap log and truncate it.
  DECLARE @Counter   INT,
          @StartTime DATETIME,
          @TruncLog  VARCHAR(255)
  SELECT  @StartTime = GETDATE(),
          @TruncLog = 'BACKUP LOG ' + db_name() + ' WITH TRUNCATE_ONLY'
  -- Try an initial shrink.
  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)  -- the log has not shrunk    
        AND (@OriginalSize * 8 /1024) > @NewSize  -- The value passed in for new size is smaller than the current size.
    BEGIN -- Outer loop.
      SELECT @Counter = 0
      WHILE  ((@Counter < @OriginalSize / 16) AND (@Counter < 50000))
        BEGIN -- update
          INSERT DummyTrans VALUES ('Fill Log')  -- Because it is a char field it inserts 8000 bytes.
          DELETE DummyTrans
          SELECT @Counter = @Counter + 1
        END   -- update
      EXEC (@TruncLog)  -- See if a trunc of the log shrinks it.
    END   -- outer loop
  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
  PRINT '*** Perform a full database backup ***'
  SET NOCOUNT OFF

  檢查記錄檔是否已經從原始大小進行壓縮。 如果有需要的話，請重複之前的步驟。如果記錄檔並未壓縮，請重新檢查文件最上方的摘要，查看是否遇到了壓縮記錄檔的常見問題。

1. 請執行主要資料庫的完整資料庫備份。
2. 再執行使用者資料庫的完整資料庫備份。這是有必要的，因為除非完成完整的資料庫備份，否則 SHRINK 命令將不會有記錄，而未來的交易記錄檔備份將變成無效。

回此頁最上方

參考

回此頁最上方