現象與風險

交易記錄 (.ldf) 無限增長會佔滿磁碟、導致資料庫掛起、備份時間暴增。根因通常為：復原模式為完整但未做記錄備份、長時間交易阻截記錄截斷、Auto Shrink 造成碎片。

三種清理方案對比

方案	適用場景	風險	停機時間
分離/附加刪除 LDF	開發/測試環境、可接受短暫離線	若資料庫損壞無法附加將導致資料遺失	秒級
DUMP TRANSACTION WITH NO_LOG + SHRINKFILE	舊版 SQL 2000/2005，需立即縮減	破壞記錄鏈，後續無法時間點還原	無需離線
切換簡單模式 + SHRINKFILE	生產環境標準作法	失去時間點還原能力，需配合完整備份策略	無需離線

生產環境推薦流程（簡單模式 + 縮減）

1. 切換復原模式

   USE master;
   ALTER DATABASE [DBName] SET RECOVERY SIMPLE;

2. 截斷記錄並縮減檔案

   -- 截斷非作用中 VLF
   DBCC SHRINKFILE ([DBName_Log], 目標_MB);

   目標大小建議設為預期正常運行大小的 1.5-2 倍，避免頻繁自動增長。

3. 切回完整模式並立即完整備份

   ALTER DATABASE [DBName] SET RECOVERY FULL;
   BACKUP DATABASE [DBName] TO DISK='...'; -- 重新建立記錄鏈起點

4. 設定記錄檔自動增長與上限

   ALTER DATABASE [DBName] MODIFY FILE (NAME=[DBName_Log], MAXSIZE=10GB, FILEGROWTH=512MB);

5. 啟用自動收縮（可選）

   ALTER DATABASE [DBName] SET AUTO_SHRINK ON; -- 謹慎使用，易產生碎片

腳本化自動化（存儲過程範例）

CREATE PROC dbo.usp_ShrinkLog
    @dbname sysname,
    @target_size_mb INT = 1024
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @sql NVARCHAR(MAX);
    -- 1. 切簡單模式
    SET @sql = 'ALTER DATABASE [' + @dbname + '] SET RECOVERY SIMPLE;';
    EXEC (@sql);
    -- 2. 縮減
    SET @sql = 'DBCC SHRINKFILE (N''' + @dbname + '_Log'', ' + CAST(@target_size_mb AS VARCHAR) + ');';
    EXEC (@sql);
    -- 3. 切回完整
    SET @sql = 'ALTER DATABASE [' + @dbname + '] SET RECOVERY FULL;';
    EXEC (@sql);
    -- 4. 立即完整備份 (略)
END;

避坑指南

• 切勿直接刪除 .ldf 檔案（除非極端災難恢復且有完整備份）。
• BACKUP LOG ... WITH TRUNCATE_ONLY 在 SQL 2008+ 已移除，改用簡單模式截斷。
• 頻繁 SHRINK 會導致索引碎片，配合 REORGANIZE/REBUILD 維護。
• 記錄檔初始大小預估足夠，減少自動增長次數（預設 10% 增長會造成效能抖動）。