SQL Server 大量交易記錄清理與壓縮完整方案

現象與風險

交易記錄 (LDF) 無限增長會佔滿磁碟、導致資料庫掛起、備份時間暴增。本文提供從「緊急瘦身」到「長期管控」的分級方案。

方案一：分離-刪除-附加（最暴力、最快、需停機）

1. SSMS 右鍵資料庫 → 任務 → 分離
2. 檔案總管刪除 .ldf 檔
3. 右鍵資料庫資料夾 → 任務 → 附加 → 僅選 .mdf → 確定
4. 系統自動建立 500KB 左右新記錄檔

T-SQL 等效：

EXEC sp_detach_db 'MyDB';
-- 手動刪除 .ldf
EXEC sp_attach_single_file_db @dbname='MyDB', @physname='C:\Data\MyDB.mdf';

方案二：截斷記錄 + DBCC SHRINKFILE（線上操作）

-- 1. 簡單模式下截斷
ALTER DATABASE MyDB SET RECOVERY SIMPLE;
DBCC SHRINKFILE (MyDB_Log, 100); -- 目標 100MB

-- 2. 完整模式需先備份記錄再截斷
BACKUP LOG MyDB TO DISK='C:\bak\MyDB_log.trn';
DBCC SHRINKFILE (MyDB_Log, 100);
ALTER DATABASE MyDB SET RECOVERY FULL; -- 還原完整模式

方案三：自動收縮與增長上限（長期治理）

-- 啟用自動收縮（慎用，會產生碎片）
ALTER DATABASE MyDB SET AUTO_SHRINK ON;

-- 限制記錄檔最大增長
ALTER DATABASE MyDB MODIFY FILE (NAME=MyDB_Log, MAXSIZE=2048MB); -- 2GB 上限

-- 固定增長增量（避免頻繁小幅擴展）
ALTER DATABASE MyDB MODIFY FILE (NAME=MyDB_Log, FILEGROWTH=512MB);

方案四：通用存儲過程（一鍵壓縮含備份）

建立於 master 資料庫，支援參數化：

USE master;
GO
CREATE PROC p_compdb
    @dbname sysname,
    @bkdatabase bit = 1,
    @bkfname nvarchar(260) = NULL
AS
BEGIN
    -- 1. 截斷記錄
    EXEC('DUMP TRANSACTION ['+@dbname+'] WITH NO_LOG');
    EXEC('BACKUP LOG ['+@dbname+'] WITH NO_LOG');
    -- 2. 收縮資料與記錄檔
    EXEC('DBCC SHRINKDATABASE(['+@dbname+'])');
    -- 3. 設定自動收縮
    EXEC('EXEC sp_dboption '+@dbname+', autoshrink, TRUE');
    -- 4. 選擇性：完整備份 -> 分離 -> 刪除 .ldf -> 附加 (極致瘦身)
    IF @bkdatabase = 1 BEGIN
        IF @bkfname IS NULL SET @bkfname = @dbname+'_'+CONVERT(varchar,GETDATE(),112)+REPLACE(CONVERT(varchar,GETDATE(),108),':','');
        EXEC('BACKUP DATABASE ['+@dbname+'] TO DISK='''+@bkfname+'''');
        -- 分離、刪除 .ldf、附加邏輯略（見原文完整代碼）
    END
END
GO

最佳實踐建議

1. 完整模式 + 定期記錄備份 是生產環境標配，不可為省空間改簡單模式
2. 記錄檔初始大小預估業務峰值，減少擴展頻率
3. AUTO_SHRINK 僅建議開發/測試環境，生產環境手動排程收縮
4. 監控 DBCC SQLPERF(LOGSPACE)，設定告警閾值 (如 > 70%)