MSSQL日誌文件暴增清理實戰：分離附加法與T-SQL腳本雙方案

資料歸檔、大批次導入、長事務未提交，常導致MSSQL日誌文件（LDF）暴增至百GB，佔滿磁碟、拖垮效能。本文提供兩套生產驗證方案：圖形化「分離-刪除-附加」與可自動化的T-SQL腳本（含Ole Automation啟用）。

方案一：分離附加法（圖形化，適合單次手工）

1. 全備份：SSMS右鍵資料庫 → 任務 → 備份（必做，防意外）
2. 分離資料庫：右鍵 → 任務 → 分離 → 勾選「刪除連接」 → 確認
3. 刪除LDF：檔案系統中刪除對應xxx_log.ldf
4. 附加資料庫：右鍵資料庫節點 → 附加 → 選中MDF → 確認（會提示找不到LDF，自動建立新1MB LDF）

風險：分離期間服務不可用；若MDF損壞無法附加，需從備份恢復。

方案二：T-SQL腳本法（可納入SSIS/作業自動化）

核心邏輯

1. 切換至master，分離資料庫（sp_detach_db）
2. 用sp_OA系列存儲過程調用Scripting.FileSystemObject刪除LDF物理文件
3. 單文件附加sp_attach_single_file_db僅掛載MDF，自動重建LDF

完整腳本

-- 1. 獲取文件路徑
DECLARE @logfilename VARCHAR(100), @datafilename VARCHAR(100);
SELECT @logfilename = physical_name FROM sys.database_files WHERE type = 1; -- log
SELECT @datafilename = physical_name FROM sys.database_files WHERE type = 0; -- data

-- 2. 啟用 Ole Automation（預設關閉，需sysadmin）
EXEC sp_configure 'show advanced options', 1; RECONFIGURE;
EXEC sp_configure 'Ole Automation Procedures', 1; RECONFIGURE;

-- 3. 切換 master 並分離
USE master;
EXEC sp_detach_db @dbname = 'TestVFA';

-- 4. 刪除 LDF 物理文件
DECLARE @Result INT, @FSO_Token INT;
EXEC @Result = sp_OACreate 'Scripting.FileSystemObject', @FSO_Token OUTPUT;
EXEC @Result = sp_OAMethod @FSO_Token, 'DeleteFile', NULL, @logfilename;
EXEC @Result = sp_OADestroy @FSO_Token;

-- 5. 單文件附加（自動重建 LDF）
EXEC sp_attach_single_file_db @dbname = 'TestVFA', @physname = @datafilename;

舊版清理命令（2005/2000，2008+已棄用/不支援）

DUMP TRANSACTION TestDB WITH NO_LOG;      -- 截斷日誌
DBCC SHRINKFILE ('TestDB_log', 1);        -- 收縮至1MB
BACKUP LOG TestDB WITH NO_LOG;            -- 同DUMP

現代替代：簡單恢復模式下DBCC SHRINKFILE；完整恢復模式需先BACKUP LOG再收縮。

根治建議：從源頭控制日誌增長

清理只是止痛，合理的恢復模式、備份策略、自動增長配置才是治本之道。