淺談 SQL Server 的交易紀錄檔
TLDR
- 交易紀錄檔 (.ldf) 負責記錄所有資料變更，確保資料一致性與完整性。
- 復原模式決定了交易紀錄檔的截斷機制與還原能力：簡單模式會自動截斷，完整與大量記錄模式則需透過交易紀錄檔備份來截斷。
- 交易紀錄檔備份是防止紀錄檔無限制增長的關鍵，且必須在執行過完整備份後才能進行。
- 壓縮檔案空間前，應先進行交易紀錄檔備份，否則空間無法有效釋放。
- 若選擇完整復原模式卻未執行交易紀錄檔備份，將導致紀錄檔持續膨脹並耗盡磁碟空間。
復原模式與交易紀錄檔截斷
在 SQL Server 中，交易紀錄檔 (.ldf) 用於記錄所有資料變更。當檢查點 (Checkpoint) 觸發時，系統會將已提交的變更寫入主資料庫檔案 (.mdf)。交易紀錄檔截斷是將已提交的交易標記為可回收的過程，若未截斷，紀錄檔將持續增長。
復原模式差異
什麼情況下會遇到紀錄檔空間管理問題？當資料庫設定為「完整」或「大量記錄」模式，卻未執行交易紀錄檔備份時。
- 完整 (Full Recovery Model)：所有交易皆會完整記錄。支援點時間還原 (Point-in-Time Recovery)。檢查點不會自動截斷，必須透過定期備份管理。
- 大量記錄 (Bulk-Logged Recovery Model)：針對批量操作使用最小記錄以提升效能。檢查點不會自動截斷，需定期備份管理。無法進行細粒度點時間還原。
- 簡單 (Simple Recovery Model)：每次檢查點時自動截斷紀錄檔。無法進行交易紀錄檔備份，僅能還原至最近的備份點。
TIP
不管是哪種模式，都不影響 Begin Transaction 的使用。
備份策略與空間管理
什麼情況下需要調整備份策略？當發現備份檔案過大，或資料庫磁碟空間因紀錄檔暴增而不足時。
備份方式建議
- 完整備份 (Full Backup)：備份整個資料庫，包含 .mdf 與 .ldf。
- 差異備份 (Differential Backup)：僅備份自上次完整備份後的變更，節省空間。
- 交易紀錄檔備份 (Transaction Log Backup)：備份自上次備份以來的所有交易，並執行截斷以釋放空間。
實務建議採用：每週完整備份、每日差異備份、每 15 分鐘交易紀錄檔備份。
檔案空間壓縮
若資料庫已設定空間過大，可使用 DBCC SHRINKFILE 進行壓縮。
sql
-- 將檔案大小收縮到 1000 MB
DBCC SHRINKFILE (檔案邏輯名稱, 1000);WARNING
壓縮的最小限制為已使用空間的大小。若要有效壓縮交易紀錄檔，建議在執行 DBCC SHRINKFILE 前先進行交易紀錄檔備份，或將還原模式暫時改為簡單模式以觸發截斷。
實務常見問題分析
什麼情況下會遇到交易紀錄檔異常增長？當資料庫設定為「完整復原模式」卻忽略了交易紀錄檔備份時。
- 交易紀錄檔無限制增長：紀錄檔會持續擴張，直到磁碟空間耗盡。
- 備份檔案過大：完整備份會包含所有未截斷的紀錄，導致備份檔體積龐大。
- 備份策略失效：若未執行交易紀錄檔備份，完整模式的優勢將無法發揮，建議改用簡單模式以提升效能。
異動歷程
- 2024-10-17 初版文件建立。