MSSQL 日誌清理與刪除：分離附加法與 T-SQL 腳本實戰

專案進行資料歸檔時，大量寫入導致交易日誌（LDF）暴增，嚴重佔用磁碟空間並拖慢效能。本文提供兩種清理方案：圖形化「分離-刪除-附加」流程，以及適合 SSIS 自動化的 T-SQL 腳本。

方案一：分離資料庫 → 刪除 LDF → 附加資料庫（圖形介面）

1. 在 SSMS 中右鍵資料庫 → 「工作」 → 「分離」，勾選「刪除現有連接」。
2. 至資料庫檔案目錄，手動刪除對應的 `.ldf` 檔案。
3. 右鍵「資料庫」 → 「附加」，選擇 `.mdf` 檔案。系統提示找不到日誌檔，確認後自動建立新日誌檔（約 500 KB）。

方案二：T-SQL 自動化腳本（適合 SSIS/排程作業）

此腳本動態取得檔案路徑，啟用 OLE Automation 以刪除實體檔案，完成全自動清理：

-- 1. 取得資料庫檔案路徑
DECLARE @logfilename VARCHAR(100), @datafilename VARCHAR(100);
SELECT @logfilename = physical_name FROM sys.database_files WHERE type = 1;
SELECT @datafilename = physical_name FROM sys.database_files WHERE type = 0;

-- 2. 切換至 master 並分離資料庫
USE master;
EXEC sp_detach_db @dbname = 'TestVFA';

-- 3. 啟用 OLE Automation Procedures（預設關閉）
EXEC sp_configure 'show advanced options', 1; RECONFIGURE;
EXEC sp_configure 'Ole Automation Procedures', 1; RECONFIGURE;

-- 4. 使用 FileSystemObject 刪除實體 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. 附加資料庫（自動重建日誌）
EXEC sp_attach_single_file_db @dbname = 'TestVFA', @physname = @datafilename;

快速清理指令（適用 SQL 2000/2005）

DUMP TRANSACTION TestDB WITH NO_LOG;      -- 清空日誌
DBCC SHRINKFILE ('TestDB_log', 1);        -- 收縮日誌檔至 1 MB
BACKUP LOG TestDB WITH NO_LOG;            -- 截斷日誌（2008+ 不支援）

注意事項

• 執行前**務必完整備份資料庫**。
• OLE Automation Procedures 屬高風險功能，用畢後建議關閉：`EXEC sp_configure 'Ole Automation Procedures', 0; RECONFIGURE;`。
• SQL Server 2008 及更新版本請改用「簡單復原模式」+ 定期備份，或執行 `DBCC SHRINKFILE` 配合記錄備份。