SQL Server 2005 交易記錄檔清理與壓縮完整指南

SQL Server 2005 交易記錄檔 (.ldf) 過大是常見維護議題。本文彙整自動/手動清理、壓縮、限制成長、復原模式切換等完整操作，並標註風險與適用版本差異。

兩大清理策略

手動清理完整步驟 (SQL 2005)

1. 清空記錄：DUMP TRANSACTION [DB] WITH NO_LOG (或 BACKUP LOG [DB] WITH NO_LOG)
2. 截斷記錄：BACKUP LOG [DB] WITH TRUNCATE_ONLY
3. 壓縮檔案：
   • GUI：右鍵資料庫 → 工作 → 收縮 → 檔案 → 選擇記錄檔 → 收縮至指定 MB (介面會顯示最小可縮值)。
   • T-SQL：DBCC SHRINKFILE ([DB_log], 1) (壓縮至 1 MB) 或 DBCC SHRINKDATABASE([DB], TRUNCATEONLY)。

極致壓縮：分離/刪除/重新附加 (高風險)

1. 分離資料庫：EXEC sp_detach_db 'DB'
2. 刪除實體 .ldf 檔案。
3. 附加單一 .mdf：EXEC sp_attach_single_file_db 'DB', '路徑\DB.mdf' → 自動建立 ~500KB 新記錄檔。

⚠️ 風險：若資料庫有未提交交易或系統資料庫損壞，可能導致資料遺失或無法附加。生產環境嚴禁使用。

長效管理設定

• 自動收縮：EXEC sp_dboption 'DB', 'autoshrink', 'true' (不建議常開，會造成索引碎片與效能抖動)。
• 限制檔案最大成長：
  • GUI：資料庫屬性 → 檔案 → 記錄檔 → 「限制檔案成長」設定上限 (MB)。
  • T-SQL：ALTER DATABASE [DB] MODIFY FILE (NAME = 邏輯名, MAXSIZE = 2048MB)。
• 定期記錄備份 (完整復原模式下)：BACKUP LOG [DB] TO DISK = '路徑\DB_log.trn'，配合維護計畫排程執行。

簡易三步驟 (簡單復原模式)

1. 右鍵資料庫 → 屬性 → 選項 → 復原模式 → 簡單。
2. 右鍵資料庫 → 工作 → 收縮 → 資料庫/檔案 → 針對記錄檔收縮。
3. 復原模式改回 大量記錄 或 完整 (視備份策略)。

資料遺失後的記錄備份還原流程

1. 誤操作前已有完整備份 + 定期差異/記錄備份。
2. 立即執行一次記錄備份：BACKUP LOG [DB] TO DISK = '尾部記錄.trn' (捕捉誤操作前的最後交易)。
3. 還原完整備份：RESTORE DATABASE [DB] FROM DISK = '全備.bak' WITH NORECOVERY。
4. 依序還原差異備份 (同 NORECOVERY)。
5. 還原記錄備份至誤操作前時間點：RESTORE LOG [DB] FROM DISK = '尾部記錄.trn' WITH STOPAT = 'YYYY-MM-DD HH:MM:SS', RECOVERY。

版本差異提醒

• SQL 2005：支援 NO_LOG/TRUNCATE_ONLY。
• SQL 2008+：移除 上述選項，改用 ALTER DATABASE SET RECOVERY SIMPLE + DBCC SHRINKFILE。