SQL Server 交易記錄檔不再增長的根本解決之道

現象與誤區

DBA 常面臨記錄檔（.ldf）無限增長問題。單純收縮（Shrink）屬治標不治本，若不解決增長根因，空間很快又會被佔滿。

方案一：切換至簡單復原模式（僅限非生產/可接受資料遺失環境）

-- SQL 2000
EXEC sp_dboption '資料庫名', 'trunc. log on chkpt.', 'TRUE';

-- SQL 2005/2008+
ALTER DATABASE [資料庫名] SET RECOVERY SIMPLE;

機制：Checkpoint 時自動截斷非作用中記錄，檔案不再增長。代價：無法進行時間點還原，誤操作無法復原，嚴禁用於生產環境。

方案二：定期交易記錄備份（生產環境標準作法）

完整/大量記錄復原模式下，BACKUP LOG 會將非作用中記錄標記為可重複使用，SQL Server 循環使用記錄檔空間，避免無限增長。

建議備份策略

• 每日 1 次完整備份，保留 7 天。
• 每 15 分鐘 1 次記錄備份，保留 2 天。
• 使用「資料庫維護計畫精靈」建立排程，並設定「刪除超過 N 天的備份」防止磁碟爆滿。

方案三：結合完整備份清理舊記錄備份

完整備份建立新的復原基準點，完整備份之前的記錄備份即可安全刪除。維護計畫自動處理保留期限，達到空間循環利用。

總結對照

最佳實務建議

1. 生產資料庫必須使用完整復原模式 + 定期記錄備份。
2. 建立維護計畫自動化備份與清理，並設定失敗通知。
3. 定期監控記錄檔使用率（DBCC SQLPERF(LOGSPACE)），提前擴容或調整備份頻率。
4. 切勿在無完整備份前提下刪除記錄檔或切換復原模式。