SQL Server 記錄檔清空與縮減兩種實用方法：簡單復原模式收縮與 T-SQL 指令碼強制截斷

方法一：簡單復原模式 + 收縮資料庫 (圖形介面)

1. 企業管理器 → 資料庫右鍵 屬性 → 選項 → 復原模式 選 簡單 → 確定。
2. 資料庫右鍵 所有工作 → 收縮資料庫 → 採用預設值 → 確定。
3. 完成後建議將復原模式改回 完整 (或大量記錄)，因記錄檔為異常復原關鍵依據。

原理：簡單模式下交易提交後記錄空間自動重複使用，收縮操作即可釋放未使用空間至作業系統。

方法二：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 Size of ' + DB_NAME() + ' LOG is ' + CAST(@OriginalSize AS varchar) + ' 8K pages or ' + 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 of ' + DB_NAME() + ' LOG is ' + CAST(size AS varchar) + ' 8K pages or ' + CAST(size*8/1024 AS varchar) + 'MB'
FROM sysfiles WHERE name = @LogicalFileName

DROP TABLE DummyTrans
SET NOCOUNT OFF

原理對比

最佳實務

• 生產環境嚴禁長期使用簡單模式，除非接受資料遺失風險。
• 定期 BACKUP LOG 至磁碟是控制記錄檔大小的根本之道。
• 記錄檔異常暴增時，先調查長時間交易、大量作業、複寫延遲等根因，再執行縮減。
• SQL Server 2008+ 已移除 TRUNCATE_ONLY，請改用簡單模式切換法或調整備份頻率。