SQL Server 交易記錄清除兩大經典方法

交易記錄（LDF）無限增長會耗盡磁碟。本文提供兩種清除縮減方案：簡單模式收縮（圖形介面）與 T-SQL 迴圈截斷（腳本自動化）。

方法一：簡單復原模式 + 收縮資料庫（GUI）

1. 企業管理器 → 資料庫屬性 → 選項 → 復原模式 = 簡單。
2. 右鍵資料庫 → 所有工作 → 收縮資料庫 → 確定（預設參數即可）。
3. 完成後建議改回 完整復原模式（保護點還原能力），並立即做完整備份啟動新記錄鏈。

注意：簡單模式下記錄自動截斷，無法進行時間點還原，僅適用維護窗口臨時操作。

方法二：T-SQL 腳本自動化截斷並縮減至指定大小

SET NOCOUNT ON;
DECLARE @logicalFileName sysname = 'yourDB_log',  -- 邏輯檔名，可用 sp_helpfile 查詢
        @maxMinutes INT = 10,                     -- 最大允許分鐘
        @newSizeMB INT = 1;                       -- 目標大小 (MB)

USE yourDB;

DECLARE @originalSize INT, @counter INT, @startTime DATETIME, @truncLog VARCHAR(255);
SELECT @originalSize = size FROM sysfiles WHERE name = @logicalFileName;
PRINT 'Original log size: ' + CAST(@originalSize * 8 / 1024 AS VARCHAR) + ' MB';

CREATE TABLE #DummyTrans (DummyColumn CHAR(8000) NOT NULL);

SELECT @startTime = GETDATE(), @truncLog = 'BACKUP LOG ' + DB_NAME() + ' WITH TRUNCATE_ONLY';

DBCC SHRINKFILE (@logicalFileName, @newSizeMB);
EXEC (@truncLog);

WHILE @maxMinutes > DATEDIFF(MI, @startTime, GETDATE())
  AND @originalSize = (SELECT size FROM sysfiles WHERE name = @logicalFileName)
  AND (@originalSize * 8 / 1024) > @newSizeMB
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 log 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 可重用。
• 迴圈寫入/刪除虛擬資料產生記錄，強制記錄檔循環，配合截斷逐步釋放空間。
• DBCC SHRINKFILE 直接釋放 OS 層面空間。

長期管理建議

• 生產庫務必使用完整復原模式 + 定期記錄備份（如每 15 分鐘），自動截斷記錄、控制大小、支援時間點還原。
• 設定記錄檔自動增長固定 MB（如 100 MB），避免頻繁微增長碎片化。
• 監控 DBCC SQLPERF(LOGSPACE)，使用率超 80% 告警。