SQL Server 清除交易記錄檔四大方法

現象

SQL Server 交易記錄檔（LDF）增長極快，長期運行會佔用大量磁碟空間，需定期清理。

方法一：BACKUP LOG WITH NO_LOG + 收縮檔案

1. 查詢分析器執行：BACKUP LOG database_name WITH NO_LOG
2. 企業管理器 → 右鍵資料庫 → 所有工作 → 收縮資料庫 → 收縮檔案 → 選擇記錄檔 → 設定收縮至建議最小值。

方法二：設定簡單復原模式自動截斷

1. 企業管理器 → 資料庫屬性 → 選項 → 故障還原模式選「簡單」。
2. 右鍵資料庫 → 所有工作 → 收縮資料庫 → 確定。
3. 完成後建議改回「完整」模式，以保留完整備份鏈。

方法三：T-SQL 腳本收縮記錄檔

SET NOCOUNT ON
DECLARE @LogicalFileName sysname, @MaxMinutes INT, @NewSize INT
USE tablename -- 目標資料庫
SELECT @LogicalFileName = 'tablename_log', -- 邏輯記錄檔名
       @MaxMinutes = 10,
       @NewSize = 1 -- 目標大小 (MB)

-- 初始化變數並建立暫存表填滿記錄檔強制截斷
DECLARE @OriginalSize int
SELECT @OriginalSize = size FROM sysfiles WHERE name = @LogicalFileName

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

SELECT 'Final Size of ' + db_name() + ' LOG is ' +
       CONVERT(VARCHAR(30), size) + ' 8K pages or ' +
       CONVERT(VARCHAR(30), (size*8/1024)) + 'MB'
FROM sysfiles WHERE name = @LogicalFileName

DROP TABLE DummyTrans
SET NOCOUNT OFF

方法四：刪除實體記錄檔（高風險）

1. 斷開所有連線。
2. 企業管理器 → 分離資料庫。
3. 刪除實體 .ldf 檔案。
4. 重新附加資料庫（會自動建立新的 500KB 記錄檔）。

警告：此法可能導致資料遺失，務必先完整備份！建議優先使用方法一。