SQL Server 交易記錄檔管理完整指南：備份、截斷、壓縮與災難恢復

交易記錄檔角色與運作機制

• 記錄所有資料庫修改動作，支援交易回復 (Rollback) 與災難恢復。
• 效能考量：資料修改先寫入記憶體緩衝區，同步寫入記錄檔 (WAL)，資料檔案稍後由 Checkpoint/Lazy Writer 寫入。
• 啟動時依記錄檔標記點 (LSN) 決定重做/回復動作，確保 ACID。

維護三大支柱

1. 定期記錄檔備份 (完整/大量記錄復原模式必須)

• 備份過程會標記非作用中虛擬記錄檔 (VLF) 可重複使用，防止記錄檔無限增長。
• 建議頻率：高交易量系統 15~60 分鐘一次。

2. 簡單復原模式自動截斷

• ALTER DATABASE dbname SET RECOVERY SIMPLE;
• 每次 Checkpoint 自動截斷非作用中 VLF，適合不需時間點恢復的測試/開發/唯讀庫。

3. 物理壓縮 (Shrink) 釋放磁碟空間

• DBCC SHRINKDATABASE：壓縮整個資料庫 (含記錄檔)。
• DBCC SHRINKFILE (N'logfile_name', target_size_MB)：精準壓縮指定記錄檔。
• ALTER DATABASE dbname SET AUTO_SHRINK ON;：自動壓縮 (生產環境不建議，易造成碎片與效能抖動)。
• 壓縮最佳時機：低尖峰時段，避免衝擊線上效能。

災難恢復架構

Log Shipping 關鍵點

• 主伺服器定期備份記錄檔 → 複製至備援伺服器 → 還原 (STANDBY/NORECOVERY)。
• 監視伺服器偵測傳送間隔逾時並記錄事件日誌。
• 常作為異地災備與報表伺服器同步來源。

效能優化建議

• 記錄檔放置於獨立磁碟/RAID 1+0，分離資料檔 I/O。
• 預先配置足夠初始大小與成長增量 (避免頻繁自動成長碎片化)。
• 監控 DBCC SQLPERF(LOGSPACE) 使用率，設定警示閾值 (如 > 80%)。
• 長時間交易會阻礙記錄檔截斷，優化批次作業切分交易。

總結

交易記錄檔是資料庫可靠性基石。建立「定期備份 → 自動截斷/手動壓縮 → 監控警示 → 災難演練」閉環管理體系，才能在故障發生時從容恢復，將業務損失降至最低。

1. 上一篇文章： SQL Server 啟動時自動執行預存程序設定指南
2. 下一篇文章： SQL Server 2005 Mobile 3.0 合併複寫與 RDA 遠端資料存取技術比較

關於SqlServer

T-SQL 進階：子查詢三大類型（資料表/欄位/純量）與資料來源應用

子查詢定義與可用子句 嵌套於外層查詢的受限 `SELECT`

SQL Server 2008 連接字串完整大全：.NET、OLE DB、ODBC、Native Client 多種提供者語法速查

快速選擇指南 提供者命名空間/驅動適用場景 SqlClie

T-SQL 一鍵刪除使用者自訂預存程序（排除系統程序）

適用情境 在開發/測試環境快速清空所有使用者建立的預存程序，

SQL Server 預設值物件建立與管理完整指南

預設值物件 vs 資料表層級 DEFAULT 預設值物件為獨