SQL Server 2008 交易記錄檔 (LDF) 空間無法釋放？終極解法：分離刪除重建

情境：LDF 檔案佔用 23GB，但 DBCC SQLPERF(LOGSPACE) 顯示實際使用 < 1%。嘗試 DBCC SHRINKFILE、切換復原模式 (FULL↔SIMPLE) 皆無法釋放空間，報錯：Cannot shrink log file 2 because of minimum log space required.

為何常規方法失效？

• 記錄檔內部虛擬記錄檔 (VLF) 結構導致無法收縮至目標大小。
• 長時間未備份記錄、大量交易、異常關機等造成 VLF 碎片化。

終極解法：分離 → 刪除 LDF → 附加重建

1. 封鎖所有寫入：應用層停止服務、或設定資料庫 READ_ONLY/SINGLE_USER。
2. 分離資料庫：SSMS 右鍵 → 工作 → 分離 → 勾選卸除連接。
3. 刪除或重命名 LDF：檔案總管刪除 xxx_log.ldf。
4. 附加資料庫：SSMS 右鍵資料庫 → 工作 → 附加 → 選擇 .mdf → 系統提示找不到記錄檔 → 移除 該記錄檔項目 → 確定。
5. 系統自動在 .mdf 同目錄建立新 .ldf (約 500KB)。

若需將 LDF 放置不同磁碟

1. 分離資料庫。
2. 將新產生的 .ldf 移動至目標磁碟。
3. 附加時在檔案清單中修正記錄檔路徑指向新位置。

T-SQL 等效流程

-- 1. 封鎖寫入
ALTER DATABASE MyDB SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
-- 2. 分離
EXEC sp_detach_db 'MyDB';
-- 3. 手動刪除/移動 LDF 檔案 (需 xp_cmdshell 或外部操作)
-- 4. 附加重建記錄檔
CREATE DATABASE MyDB 
ON (FILENAME = 'D:\Data\MyDB.mdf')
FOR ATTACH_REBUILD_LOG;
GO

代價與風險

• 停機時間：分離期間資料庫完全不可用。
• 記錄鏈中斷：重建記錄檔後，無法套用舊記錄備份進行時間點還原，必須立即執行 完整備份 重新建立記錄鏈。
• 複寫/鏡像/AlwaysOn：此操作會破壞拓撲，需重新設定。

預防勝於治療

1. 定期 BACKUP LOG (如每 15 分鐘) 截斷記錄，防止 VLF 暴增。
2. 設定 MAXSIZE 限制記錄檔上限。
3. 監控 DBCC SQLPERF(LOGSPACE) 與 VLF 數量 (DBCC LOGINFO)，VLF > 1000 時考慮縮減初始大小與成長增量。
4. 非關鍵資料庫改用 SIMPLE 復原模式。

分離刪除重建是「最後手段」，務必在維護視窗執行並立即補完整備份。