交易記錄檔角色
記錄所有資料庫修改操作，確保 ACID 特性。每筆交易從 BEGIN 到 COMMIT/ROLLBACK 完整記錄。支援：
- 交易復原（Rollback）
- 資料庫還原至特定時間點
- 高可用性技術（鏡像、AlwaysOn、Log Shipping、異動複寫）
常見問題：記錄檔無限成長
- 預設復原模式為完整復原模式，若未定期進行記錄備份，記錄檔會持續增長佔滿磁碟。
- 簡單復原模式下，檢查點自動截斷非作用部分，但無法進行時間點還原。
維護三部曲：備份 → 截斷 → 壓縮
1. 定期記錄備份（核心）
BACKUP LOG [MyDB] TO DISK = 'D:\Backup\MyDB_Log_20240115.trn'
WITH COMPRESSION, CHECKSUM;
- 備份完成後，對應的虛擬記錄檔（VLF）標記為非作用，可被覆寫。
- 建議頻率：依 RPO 定，通常 15-60 分鐘一次。
2. 截斷與壓縮的差異
| 動作 | 效果 | 釋放磁碟空間 |
|---|
| 截斷 | 標記 VLF 為非作用，允許循環覆寫 | 否（檔案大小不變） |
| 壓縮 | 實際移除檔案尾端非作用 VLF，縮減檔案大小 | 是 |
3. 壓縮指令
-- 壓縮特定資料庫的記錄檔（指定目標大小 MB）
DBCC SHRINKFILE (N'MyDB_Log', 1024); -- 縮至 1GB
-- 或壓縮整個資料庫（含資料檔）
DBCC SHRINKDATABASE (MyDB, 10); -- 保留 10% 可用空間
- 壓縮為線上操作，但會產生 I/O 與鎖定，建議離峰時段執行。
- 頻繁壓縮會導致檔案反覆增長/縮減（碎片化），建議設定合理初始大小與自動成長增量（固定 MB，非百分比），減少壓縮需求。
復原模式選擇指南
| 模式 | 記錄備份 | 時間點還原 | 適用場景 |
|---|
| 完整 | 必要 | 支援 | 生產環境、需最小資料遺失 |
| 大量記錄 | 必要 | 支援（部分大量作業最小記錄） | 大量匯入/索引重建期間暫時切換 |
| 簡單 | 不可 | 不支援 | 開發/測試、資料可重載、RPO=24hr |
災難復原：記錄備份還原流程
- 還原完整備份：
RESTORE DATABASE MyDB FROM DISK='...' WITH NORECOVERY, REPLACE;
- 依序還原差異備份（如有）：
WITH NORECOVERY
- 依時間順序還原所有記錄備份：
WITH NORECOVERY
- 最後一筆記錄備份可用
WITH RECOVERY 或 STOPAT = '時間點' 完成還原。
Log Shipping：異地備援方案
- 主伺服器定期備份記錄 → 複製至備援伺服器 → 自動還原（
STANDBY 模式可唯讀查詢）。
- 提供資料庫層級的高可用性與災難復原，RTO 取決於備份頻率與網路傳輸時間。
最佳實務清單
- 所有生產資料庫設為完整復原模式並建立記錄備份排程。
- 監控記錄檔使用率（
DBCC SQLPERF(LOGSPACE)），設定警示 > 80%。
- 預先配置足夠初始大小（如 4GB），自動成長設為固定 512MB/1GB。
- 定期檢視 VLF 數量（
DBCC LOGINFO），過多 VLF（>1000）會拖慢啟動/還原，需重建記錄檔。
- 備份檔案異地儲存，定期演練還原流程。
- 上一篇文章： SQL Server 2008 表值參數完整教學：建立、傳遞與效能優勢
- 下一篇文章： SQL Server 計算累計餘額：銀行帳戶交易範例
Copyright ©
程式碼之家 All Rights Reserved
