萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> MySQL 教學

SQL Server 2005 交易記錄檔 (LDF) 縮減與清理實作指南

問題現象

資料庫長期運行未維護,交易記錄檔 (`.ldf`) 膨脹至數 GB 甚至佔滿磁碟,導致備份檔過大、還原耗時、磁碟空間不足。

簡單復原模式下的快速縮減 (適用開發/測試/唯讀報表庫)

-- 1. 切換至簡單復原模式 (自動截斷非作用中記錄)
ALTER DATABASE [YourDB] SET RECOVERY SIMPLE;
GO

-- 2. 縮減記錄檔至目標大小 (例如 100MB)
DBCC SHRINKFILE (N'YourDB_Log', 100); -- 邏輯檔名可用 sp_helpfile 查詢
GO

-- 3. (可選) 切回完整復原模式並立即完整備份建立基準
ALTER DATABASE [YourDB] SET RECOVERY FULL;
BACKUP DATABASE [YourDB] TO DISK = 'Path\Full.bak' WITH INIT, COMPRESSION;
BACKUP LOG [YourDB] TO DISK = 'Path\Log.trn' WITH INIT, COMPRESSION;
GO

完整復原模式下的正規縮減流程 (生產環境必須)

  1. 記錄備份: `BACKUP LOG [DB] TO DISK = '...' WITH COMPRESSION;` (將作用中 VLF 變為非作用中)。
  2. 縮減檔案: `DBCC SHRINKFILE (LogFileName, TargetSizeMB);`
  3. 立即再次記錄備份: 確保新建立的 VLF 連續性,避免下次自動增長碎片化。

舊版指令 (SQL 2000/2005 早期文獻常見,現不建議)

DUMP TRANSACTION DBName WITH NO_LOG;      -- 等同 BACKUP LOG ... WITH TRUNCATE_ONLY
DBCC SHRINKDATABASE('DBName', TRUNCATEONLY); -- 僅釋放檔案末端未使用空間,不移動頁面

警告: `NO_LOG` / `TRUNCATE_ONLY` 在 SQL Server 2008 起已**移除**。使用會中斷記錄鏈,導致後續記錄備份失敗,必須立即執行完整備份重建基準。

根治膨脹的長期策略

  1. 定期記錄備份排程: SQL Agent 作業每 15-60 分鐘執行 `BACKUP LOG`,防止 VLF 累積。
  2. 預設檔案大小與成長增量: 設定合理初始大小 (如 1GB)、固定增長量 (如 256MB/512MB) **避免百分比成長**。
  3. 監控 VLF 數量: `DBCC LOGINFO` 查看 VLF 數目,超過 1000 會影響啟動/還原/備份效能。
  4. 長時間交易監控: `DBCC OPENTRAN` 偵測阻塞記錄截斷的舊交易。

驗證腳本

SELECT 
    name AS LogicalName,
    size/128.0 AS CurrentSizeMB,
    FILEPROPERTY(name, 'SpaceUsed')/128.0 AS UsedSpaceMB,
    (size - FILEPROPERTY(name, 'SpaceUsed'))/128.0 AS FreeSpaceMB
FROM sys.database_files
WHERE type_desc = 'LOG';
copyright © 萬盛學電腦網 all rights reserved