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

交易記錄檔核心角色

記錄資料庫所有變更操作，支援：

• ACID：交易失敗時回滾、系統崩潰後重做/回復。
• 時間點恢復：配合完整備份，可還原至任意時間點。
• 複寫/鏡像/AlwaysOn：作為資料同步來源。

運作機制

• 寫入優先：變更先寫記憶體緩衝區，同步寫入記錄檔（WAL），資料檔案稍後由檢查點背景寫入。
• 虛擬記錄檔 (VLF)：實體記錄檔內部分為多個 VLF，循環使用。
• 截斷：將非作用中 VLF 標記為可覆寫（不釋放磁碟空間）。
• 壓縮：實際釋放檔案尾端未使用空間，縮減實體大小。

常見問題：記錄檔暴增

維護策略

1. 選擇正確復原模式

• 完整模式：需定期記錄檔備份，支援時間點恢復。生產標準。
• 大量記錄模式：大量作業最小記錄，仍需記錄檔備份。
• 簡單模式：檢查點自動截斷，不支援時間點恢復，適唯讀/測試庫。

2. 定期記錄檔備份（完整/大量記錄模式必須）

BACKUP LOG [DBName] TO DISK = 'path\DBName_log.trn' WITH COMPRESSION

建議頻率：15-60 分鐘視 RPO 而定。

3. 監控與警報

-- 檢查記錄檔使用率
DBCC SQLPERF(LOGSPACE)

-- 檢查 VLF 數量與大小（過多 VLF 影響啟動/備份/恢復速度）
DBCC LOGINFO

4. 壓縮記錄檔（謹慎操作）

-- 僅在確認記錄已截斷（備份完成）且確有釋放空間需求時
DBCC SHRINKFILE (N'DBName_log', target_size_MB)

• 不要 設定自動壓縮（AUTO_SHRINK），會造成碎片、效能抖動。
• 壓縮後記錄檔會重新增長，預先規劃合理初始大小與成長增量（建議固定 MB，非百分比）。

災難恢復：記錄檔備份還原流程

1. 還原完整備份 WITH NORECOVERY。
2. 按時間順序還原差異備份（若有）WITH NORECOVERY。
3. 依序還原所有記錄檔備份 WITH NORECOVERY。
4. 最後一筆記錄檔可用 WITH RECOVERY 或 STOPAT 指定時間點。

Log Shipping 架構

主伺服器定期備份記錄檔 → 傳送至備援伺服器 → 自動還原（STANDBY 模式可唯讀查詢）。監視伺服器偵測傳送間隔逾時並發出警報。適合異地災備與報表卸載。

總結檢查清單

• [ ] 復原模式符合業務 RPO/RTO
• [ ] 完整備份 + 定期記錄檔備份作業正常
• [ ] 記錄檔初始大小/成長增量預先規劃
• [ ] 監控 VLF 數量（建議 < 1000）與使用率
• [ ] 定期演練時間點恢復

1. 上一篇文章： SQL Server 取得客戶端 IP、MAC 位址與 IP 格式化實用腳本
2. 下一篇文章： 資料庫攻擊手法解析：SQL 注入、權限提升、後門植入與防禦對策

關於SqlServer

SSAS 模式部署錯誤排查：多維與表格模式的相容性問題

在使用 SSDT 將 SSAS 專案部署至本地伺服器時，可能

SQL Server ROW_NUMBER() 進階應用：不排序、分頁、分組編號與實戰案例

ROW_NUMBER() 是 SQL Server 2005

深入掌握 SQL Server 2008 Profiler：追蹤、效能分析、自動化與最佳實務

SQL Server Profiler 是強大的圖形化追蹤工

SQL Server 2005 官方技術資源與文件連結彙總

SQL Server 2005 相關官方技術資源連結： 產