為何日誌會暴增？

• 大量資料歸檔/遷移（大量 INSERT/UPDATE/DELETE）
• 完整復原模式下未定期備份交易日誌
• 長時間執行的交易阻塞日誌截斷

日誌檔 (LDF) 膨脹會佔滿磁碟、拖慢備份/還原、影響效能。

方法一：分離附加法（圖形化/手工）

1. 完整備份資料庫（務必！）
2. SSMS → 右鍵資料庫 → 任務 → 分離 → 勾選「刪除連接」
3. 檔案系統刪除 *.ldf 檔案
4. SSMS → 右鍵資料庫 → 任務 → 附加 → 選擇 *.mdf → 確認（自動建立新 1 MB LDF）

風險：離線期間業務中斷；若 MDF 損壞則資料全失。

方法二：T-SQL 指令法（可腳本化、納入 SSIS）

舊版（SQL 2000/2005/2008）

-- 截斷日誌
BACKUP LOG TestDB WITH NO_LOG;
-- 或
DUMP TRANSACTION TestDB WITH NO_LOG;

-- 收縮日誌檔至 1 MB
DBCC SHRINKFILE ('TestDB_log', 1);

注意：SQL Server 2008 起 WITH NO_LOG 已移除。

新版（SQL 2008+）標準流程

-- 1. 切換至簡單復原模式（自動截斷非作用中 VLF）
ALTER DATABASE TestDB SET RECOVERY SIMPLE;

-- 2. 收縮日誌檔
DBCC SHRINKFILE (N'TestDB_log', 1);  -- 目標 1 MB

-- 3. 切回完整復原模式（生產環境必須）
ALTER DATABASE TestDB SET RECOVERY FULL;

-- 4. 立即進行完整備份 + 日誌備份，啟動新日誌鏈
BACKUP DATABASE TestDB TO DISK = '...';
BACKUP LOG TestDB TO DISK = '...';

自動化腳本：分離附加法（SSIS/Job 可直接呼叫）

DECLARE @dbname sysname = 'TestVFA';
DECLARE @logfile nvarchar(260), @datafile nvarchar(260);

-- 取得檔案路徑
SELECT @logfile = physical_name FROM sys.database_files WHERE type = 1 AND name = @dbname + '_log';
SELECT @datafile = physical_name FROM sys.database_files WHERE type = 0 AND name = @dbname;

-- 分離
USE master;
EXEC sp_detach_db @dbname = @dbname;

-- 刪除 LDF（需啟用 Ole Automation）
DECLARE @fso int, @hr int;
EXEC @hr = sp_OACreate 'Scripting.FileSystemObject', @fso OUT;
EXEC @hr = sp_OAMethod @fso, 'DeleteFile', NULL, @logfile;
EXEC @hr = sp_OADestroy @fso;

-- 附加單一 MDF（自動建立新 LDF）
EXEC sp_attach_single_file_db @dbname = @dbname, @physname = @datafile;

啟用 Ole Automation Procedures

EXEC sp_configure 'show advanced options', 1; RECONFIGURE;
EXEC sp_configure 'Ole Automation Procedures', 1; RECONFIGURE;

最佳實踐建議

1. 預防勝於治療：完整復原模式 + 定期日誌備份（每 15–60 分鐘）+ 監控日誌空間使用率。
2. 不可直接刪除 LDF：會導致資料庫進入 SUSPECT 狀態，修復極其麻煩。
3. 收縮後立即完整備份：重新建立日誌鏈，確保時間點還原能力。
4. 大量載入時：暫切簡單模式 → 載入 → 切回完整模式 → 完整備份。

總結

日誌暴增首選「切簡單模式 → SHRINKFILE → 切回完整模式 → 完整備份」標準流程；分離附加法僅限維護窗口且無其他選項時使用。自動化請納入 Agent Job，並啟用日誌空間監控告警。