SQL Server 記錄檔清理兩大法：收縮資料庫與 T-SQL 腳本深度解析

SQL Server 交易記錄檔（LDF）隨著時間增長，若不定期維護將佔用大量磁碟空間，甚至導致資料庫無法寫入。本文詳細說明兩種清理記錄檔的方法：圖形介面收縮與 T-SQL 腳本自動化，並解析各自適用場景與風險控制。

方法一：透過 SSMS 圖形介面收縮（適用偶發性維護）

1. 切換復原模式為「簡單」：在 SSMS 物件總管右鍵資料庫 → 屬性 → 選項 → 復原模式選「簡單」 → 確定。
2. 執行收縮資料庫：右鍵資料庫 → 所有工作 → 收縮 → 資料庫 → 直接按確定（預設參數通常足夠）。
3. 還原復原模式為「完整」：收縮完成後，重複步驟 1 將模式改回「完整」，確保後續可進行時間點還原。

方法二：T-SQL 腳本自動化收縮（適用排程作業/大型記錄檔）

以下腳本可精確控制目標大小、等待時間，並透過建立虛擬交易強制記錄檔截斷：

SET NOCOUNT ON;
DECLARE @LogicalFileName sysname = 'yourDB_log',  -- 邏輯記錄檔名稱
        @MaxMinutes INT = 10,                     -- 最大等待分鐘數
        @NewSize INT = 1;                         -- 目標大小 (MB)

USE yourDB;  -- 目標資料庫

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;

關鍵參數說明

最佳實務建議

• 定期排程交易記錄備份（完整模式下），自然截斷 VLF，減少手動收縮需求。
• 避免頻繁收縮/增長循環，造成檔案碎片與效能抖動；預先規劃適當初始大小與自動成長增量。
• 收縮前務必完整備份資料庫，腳本執行期間監控磁碟 I/O 與封鎖情況。

掌握這兩種方法，並配合定期記錄備份策略，即能有效控制 SQL Server 記錄檔體積，確保資料庫穩定運行。