SQL Server 交易記錄檔清除兩大法：簡單模式收縮 vs T-SQL 腳本深度縮小

記錄檔過大是常見維運痛點。提供兩種清除方案：

方法一：簡單模式收縮（GUI 操作，適合開發/測試）

1. 企業管理器 → 資料庫屬性 → 選項 → 復原模式設為「簡單」
2. 右鍵資料庫 → 所有工作 → 收縮資料庫 → 確定
3. 完成後改回「完整」模式（保留時間點還原能力）

原理： 簡單模式下記錄檔自動截斷，DBCC SHRINKDATABASE 釋放未使用空間。

方法二：T-SQL 深度縮小腳本（生產環境緊急處理）

SET NOCOUNT ON
DECLARE @LogicalFileName sysname, @MaxMinutes INT, @NewSize INT

USE YourDatabase                  -- 修改：目標資料庫
SELECT @LogicalFileName = 'YourDatabase_log',  -- 修改：邏輯記錄檔名
       @MaxMinutes = 10,
       @NewSize = 1                -- 修改：目標大小 (MB)

DECLARE @OriginalSize INT
SELECT @OriginalSize = size FROM sysfiles WHERE name = @LogicalFileName
PRINT 'Original: ' + 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: ' + CAST(size * 8 / 1024 AS VARCHAR) + ' MB'
FROM sysfiles WHERE name = @LogicalFileName

DROP TABLE DummyTrans
SET NOCOUNT OFF

核心邏輯解析

1. SHRINKFILE 嘗試直接縮小
2. BACKUP LOG ... TRUNCATE_ONLY 截斷非作用中 VLF
3. 若未達標，寫入/刪除虛擬資料「推進」記錄檔尾端，再截斷
4. 限制 10 分鐘與 50,000 迴圈防無限執行

關鍵參數修改表

根本解決之道

• 維持完整復原模式 + 定期 BACKUP LOG 至磁碟/帶庫
• 監控記錄檔成長、設定自動成長合理增量（避免頻繁擴展）
• 長時間交易、大量異動需拆分批次

方法一適合非生產環境快速處理；方法二適用生產緊急縮小。長期仍需建立完整備份策略。