MS SQL 記錄檔暴增緊急處理與長期管控標準作業程序

緊急處理三步驟 (生產環境可接受風險)

1. 截斷記錄 (邏輯清理)：

   BACKUP LOG MyDB WITH NO_LOG;  -- SQL 2000/2005
   -- SQL 2008+ 已移除 NO_LOG，改用暫時切換復原模式：
   ALTER DATABASE MyDB SET RECOVERY SIMPLE;
   CHECKPOINT;  -- 強制截斷
   -- 或
   DBCC SHRINKFILE (MyDB_Log, 100); -- 目標大小 MB，需先截斷才能收縮

2. 收縮實體檔案 (歸還磁碟)：
   • SSMS：右鍵資料庫 → 工作 → 收縮 → 檔案 → 選「記錄檔」→「收縮至」→ 輸入最小可用值。
   • T-SQL：`DBCC SHRINKFILE (2, 100);` (2 為記錄檔 file_id，可用 `SELECT * FROM sys.database_files` 確認)。
3. 極端情況：分離-刪除記錄檔-重新附加 (僅限開發/測試/有完整備份可重建)：

   EXEC sp_detach_db 'MyDB';
   -- 檔案總管刪除 MyDB_log.ldf
   EXEC sp_attach_single_file_db @dbname = 'MyDB',
       @physname = 'D:\Data\MyDB.mdf';  -- 自動建立 500KB 新記錄檔

   高風險：未提交交易永久丟失、資料庫可能進入 SUSPECT、啟動失敗。生產嚴禁。

長期管控四大機制

1. 自動收縮 (謹慎啟用)：

   EXEC sp_dboption 'MyDB', 'autoshrink', 'TRUE';
   -- 或 SSMS：資料庫屬性 → 選項 → 自動收縮 = True

   **副作用**：背景執行緒定期檢查、收縮造成索引碎片、效能抖動。**建議關閉**，改用定期維護計劃手動控制。

2. 限制檔案最大成長：

   ALTER DATABASE MyDB MODIFY FILE (NAME = MyDB_Log, MAXSIZE = 50GB); -- 依磁碟容量設定上限
   -- SSMS：資料庫屬性 → 檔案 → 記錄檔 → 限制檔案增長 (MB)

   防止失控佔滿磁碟導致資料庫掛起。達上限後寫入失敗，需手動清理/擴充後恢復。

3. 簡單復原模式 (非關鍵/可重建資料)：

   ALTER DATABASE MyDB SET RECOVERY SIMPLE;
   -- 或 SSMS：屬性 → 選項 → 故障還原模型 = 簡單

   Checkpoint 自動截斷，記錄檔維持穩定。**代價**：無法時間點還原，僅能還原至最近完整/差異備份。

4. 標準維護計劃 (生產黃金標準)：
   • 完整備份：每日/每週。
   • **記錄備份：每 15-60 分鐘** (自動截斷、可時間點還原、RPO 可控)。
   • 索引重組/重建：每週 (配合 `UPDATE STATISTICS`)。
   • `DBCC CHECKDB`：每週 (實體/邏輯完整性)。
   • 清理歷史備份：保留 7-14 天。

簡易三步驟收縮法 (網路流傳，適用簡單模式切換場景)

1. 屬性 → 故障還原模型 → **簡單**。
2. 右鍵資料庫 → 所有工作 → 收縮資料庫 → 確定。
3. 屬性 → 故障還原模型 → **大量記錄** (或完整，視需求)。

本質同上：切簡單模式觸發 Checkpoint 截斷 → 收縮 → 切回。操作簡單但原理相同，生產環境請依標準維護計劃執行。