萬盛學電腦網

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

SQL Server 交易記錄與資料檔壓縮完整操作指南(含風險提示)

資料庫長期運行後,交易記錄 (.ldf) 常暴增佔滿磁碟。本文提供分級壓縮方案,**嚴禁跳躍執行高風險步驟**,請按順序評估執行。

⚠️ 風險等級標示

  • 🟢 低風險:標準維護操作
  • 🟡 中風險:可能影響效能或需停機
  • 🔴 高風險:可能導致資料遺失、資料庫損毀,非必要勿用

第 1 級:標準清空記錄 🟢

-- 簡單模式下自動截斷;完整模式需先備份記錄
BACKUP LOG 資料庫名 WITH NO_LOG;  -- 舊版語法,新版建議改簡單模式或備份後再收縮
-- 或
DUMP TRANSACTION 資料庫名 WITH NO_LOG;

⚠️ NO_LOG 已棄用,現代版本請改用:
ALTER DATABASE 資料庫名 SET RECOVERY SIMPLE; → 收縮 → ALTER DATABASE 資料庫名 SET RECOVERY FULL;

第 2 級:收縮檔案 (標準) 🟢

GUI:企業管理器/SSMS → 右鍵資料庫 → 工作 → 收縮 → 檔案 → 選記錄檔/資料檔 → 「收縮至」輸入建議最小值。
T-SQL:

-- 收縮整個資料庫
DBCC SHRINKDATABASE (資料庫名);

-- 收縮指定檔案 (1=資料檔, 2=記錄檔,確認 sysfiles)
DBCC SHRINKFILE (2, 目標MB數);  -- 記錄檔通常是檔案 2

第 3 級:分離-刪除記錄檔-重新附加 🔴 (極高風險)

原理:分離資料庫 → 手動刪除 .ldf → 附加單一 .mdf → SQL Server 自動建立極小 (500KB+) 新記錄檔。
後果:所有未提交交易遺失、交易鏈斷裂、可能導致資料不一致。僅限測試環境或已有完整備份且接受資料遺失時使用。

-- T-SQL 方式
EXEC sp_detach_db @dbname = '資料庫名';
-- 手動刪除 .ldf 檔案
EXEC sp_attach_single_file_db @dbname = '資料庫名',
    @physname = 'C:\Path\To\資料庫名.mdf';

第 4 級:設定自動收縮 🟡 (生產環境不建議)

EXEC sp_dboption '資料庫名', 'autoshrink', 'TRUE';

或 SSMS:資料庫屬性 → 選項 → 自動收縮 = True。
缺點:碎片化嚴重、效能抖動、無法控制時機。生產環境建議**手動排程收縮**。

第 5 級:限制記錄檔最大成長 🟡

-- 限制記錄檔最大 20MB (視業務調整)
ALTER DATABASE 資料庫名 MODIFY FILE (NAME = 邏輯記錄檔名, MAXSIZE = 20MB);

⚠️ 達上限後寫入會失敗,**必須搭配定期記錄備份/收縮**,否則業務中斷。

最佳實踐建議

  1. 生產庫採用 完整復原模式 + 定期記錄備份 (每 15-60 分鐘),記錄檔自動循環覆蓋,無需手動收縮
  2. 若記錄檔異常暴增,先查 DBCC OPENTRAN 找長期未提交交易,再查 log_reuse_wait_desc 確認阻礙原因
  3. 收縮屬**應急手段**,非常態運維。頻繁收縮 → 碎片化 → 效能下降 → 再收縮 → 惡性循環

總結

按 1 → 2 → 評估風險 → 3/4/5 順序執行。**永遠優先備份**,**永遠在測試環境驗證高風險步驟**。資料安全大於磁碟空間。

copyright © 萬盛學電腦網 all rights reserved