SQL Server 交易記錄清理與壓縮完整指南：四大方法與版本差異

核心原則

操作前必須完整備份資料庫！ 記錄檔在異常復原中至關重要，清理前請確認備份可用。

方法一：BACKUP LOG WITH NO_LOG + 收縮檔案（經典）

-- 1. 截斷記錄（SQL 2005/2008 不再支援 NO_LOG，改用簡單模式）
BACKUP LOG DatabaseName WITH NO_LOG;  -- 僅 SQL 2000/2005 相容模式

-- 2. SSMS 圖形介面收縮
-- 右鍵資料庫 → 工作 → 收縮 → 檔案
-- 檔案類型選「記錄」 → 「釋放未使用空間前重組頁面」 → 輸入最小目標大小

方法二：切換復原模式自動截斷

1. SSMS → 資料庫屬性 → 選項 → 復原模式：簡單
2. 右鍵資料庫 → 工作 → 收縮 → 資料庫（預設值即可）
3. 收縮完成後，將復原模式改回 完整（生產環境必須）

注意：簡單模式下記錄自動截斷，無法進行時間點還原。僅適用於非生產或可接受資料遺失的環境。

方法三：T-SQL 腳本精確控制記錄大小

SET NOCOUNT ON;
DECLARE @LogicalFileName sysname = 'DatabaseName_log',  -- 邏輯記錄檔名
        @MaxMinutes INT = 10,
        @NewSize INT = 1;  -- 目標大小 (MB)

USE DatabaseName;
DECLARE @OriginalSize INT = (SELECT size FROM sys.database_files WHERE name = @LogicalFileName);
CREATE TABLE #DummyTrans (DummyColumn CHAR(8000) NOT NULL);

DECLARE @Counter INT, @StartTime DATETIME = GETDATE(), @TruncLog NVARCHAR(255);
SET @TruncLog = N'BACKUP LOG ' + QUOTENAME(DB_NAME()) + N' WITH TRUNCATE_ONLY';

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

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

SELECT 'Final Log Size (MB): ' + CAST((size * 8 / 1024) AS VARCHAR(30))
FROM sys.database_files WHERE name = @LogicalFileName;
DROP TABLE #DummyTrans;
SET NOCOUNT OFF;

方法四：卸離-刪除記錄檔-重新附加（高風險）

1. 中斷所有連線
2. SSMS → 右鍵資料庫 → 工作 → 卸離（勾選「更新統計資料」）
3. 刪除實體 .ldf 檔案
4. 右鍵「資料庫」 → 附加 → 選擇 .mdf，系統自動建立新記錄檔（約 500KB）

高風險警告：記錄檔未寫入資料頁的交易將永久遺失。僅限測試環境或確認無作用中交易時使用。

版本專用指令速查

替代工具

SQL Server 記錄清除專家 3.5：支援 6.5~2005 版本，一鍵壓縮至 1MB，適合不想手動操作的 DBA。

最佳實務建議

1. 生產環境嚴禁使用方法四
2. 定期執行 BACKUP LOG 維持記錄檔大小穩定
3. 監控 DBCC SQLPERF(LOGSPACE) 追蹤記錄使用率
4. 大量異動操作（匯入、重建索引）前暫時切換至大量記錄模式
5. 記錄檔初始大小與成長增量預設合理值，避免頻繁自動成長碎片化