SQL Server 日誌暴漲處理：簡單模式收縮與 T-SQL 深度清理雙方案

日志文件（LDF）失控增長是常見故障。提供圖形化與腳本化兩套方案，按場景選擇。

方案一：企業管理器簡單模式收縮（適合日常維護）

1. 屬性 → 選項 → 恢復模式 → 簡單
2. 右鍵數據庫 → 所有任務 → 收縮數據庫（默認參數）
3. 完成後恢復模式改回完整/大量日誌

原理：簡單模式下檢查點自動截斷非活動日誌，配合收縮釋放物理空間。

方案二：T-SQL 深度清理腳本（頑固大日誌、自動化運維）

SET NOCOUNT ON
DECLARE @LogicalFileName sysname, @MaxMinutes INT, @NewSize INT
USE 目標數據庫
SELECT @LogicalFileName = '數據庫_log', @MaxMinutes = 10, @NewSize = 1 -- 目標 1MB

DECLARE @OriginalSize INT
SELECT @OriginalSize = size FROM sysfiles WHERE name = @LogicalFileName
PRINT 'Original Size: ' + CAST(@OriginalSize*8/1024 AS VARCHAR) + 'MB'

CREATE TABLE DummyTrans (DummyColumn CHAR(8000) NOT NULL)
DECLARE @Counter INT, @StartTime DATETIME, @TruncLog VARCHAR(255)
SELECT @StartTime = GETDATE(), @TruncLog = 'BACKUP LOG ' + DB_NAME() + ' WITH TRUNCATE_ONLY'

DBCC SHRINKFILE (@LogicalFileName, @NewSize)
EXEC (@TruncLog)

WHILE @MaxMinutes > DATEDIFF(mi, @StartTime, GETDATE())
  AND @OriginalSize = (SELECT size FROM sysfiles WHERE name = @LogicalFileName)
  AND (@OriginalSize * 8 / 1024) > @NewSize
BEGIN
  SELECT @Counter = 0
  WHILE @Counter < @OriginalSize / 16 AND @Counter < 50000
  BEGIN
    INSERT DummyTrans VALUES ('Fill Log')
    DELETE DummyTrans
    SELECT @Counter = @Counter + 1
  END
  EXEC (@TruncLog)
END

PRINT 'Final Size: ' + CAST((SELECT size FROM sysfiles WHERE name = @LogicalFileName)*8/1024 AS VARCHAR) + 'MB'
DROP TABLE DummyTrans
SET NOCOUNT OFF

腳本核心邏輯

• BACKUP LOG ... WITH TRUNCATE_ONLY：標記非活動 VLF 可覆寫（SQL 2008+ 已移除，改用簡單模式臨時切換）
• 造數據填滿日誌 → 強制循環 → 再次截斷 → 物理收縮
• 超時保護（@MaxMinutes）防止無限循環

根治建議

• 定期日誌備份（完整模式下唯一合法截斷方式）
• 監控 log_reuse_wait_desc 識別截斷阻塞原因
• 預設合理初始大小與自動增長增量（避免頻繁小幅增長產生大量 VLF）