SQL Server 日志清理兩大實戰方案：收縮數據庫法與 T-SQL 腳本法深度對比

SQL Server 日志清理兩大實戰方案

SQL Server 事務日志 (.ldf) 長期無維護會無限增長，佔滿磁盤導致服務掛起。本文對比 **SSMS 圖形化收縮法** 與 **T-SQL 腳本精準收縮法**，給出生產環境安全操作 SOP，並解析「為什麼不建議直接刪除日志文件」。

方案一：SSMS 圖形化收縮 (適合臨時、小型庫)

1. SSMS 連接實例 → 右鍵目標庫 → **屬性** → **選項** → **恢復模式** 改為 **簡單** → 確定。
2. 右鍵目標庫 → **任務** → **收縮** → **文件** → 文件類型選 **日志** → 釋放未使用空間 / 收縮至指定大小 → 確定。
3. 操作完成後，**恢復模式改回完整** (生產環境必須)，建立完整備份鏈。

優點：可視化、無需寫代碼。
缺點：無法精確控制收縮後大小、大日志收縮耗時長且阻塞、簡單模式下斷點恢復能力丟失。

方案二：T-SQL 精準收縮腳本 (生產環境標準化、可自動化、可監控)

SET NOCOUNT ON;
DECLARE
    @LogicalFileName sysname = 'YourDB_log',  -- 邏輯日志文件名 (非物理路徑)
    @MaxMinutes INT = 10,                     -- 最大允許包裝時間
    @NewSizeMB INT = 512;                     -- 目標收縮大小 (MB)

USE YourDB;  -- 切換到目標庫

-- 1. 記錄原始大小
DECLARE @OriginalSizeMB INT;
SELECT @OriginalSizeMB = size * 8 / 1024.0
FROM sys.database_files
WHERE name = @LogicalFileName AND type = 1;  -- type=1 日志文件

PRINT 'Original Log Size: ' + CAST(@OriginalSizeMB AS varchar(10)) + ' MB';

-- 2. 創建虛擬表填充日志強制截斷 (簡單模式下)
CREATE TABLE #DummyTrans (DummyColumn char(8000) NOT NULL);

DECLARE @Counter INT = 0, @StartTime DATETIME = GETDATE();
DECLARE @TruncLog NVARCHAR(MAX) = 'BACKUP LOG [' + DB_NAME() + '] WITH TRUNCATE_ONLY';

-- 3. 先嘗試直接 DBCC SHRINKFILE
DBCC SHRINKFILE (@LogicalFileName, @NewSizeMB);
EXEC (@TruncLog);

-- 4. 迴圈填充日志並截斷，直到達到目標或超時
WHILE @MaxMinutes > DATEDIFF(MI, @StartTime, GETDATE())
  AND @OriginalSizeMB = (SELECT size * 8 / 1024.0 FROM sys.database_files WHERE name = @LogicalFileName AND type = 1)
  AND (SELECT size * 8 / 1024.0 FROM sys.database_files WHERE name = @LogicalFileName AND type = 1) > @NewSizeMB
BEGIN
    SET @Counter = 0;
    WHILE @Counter < @OriginalSizeMB / 16 AND @Counter < 50000
    BEGIN
        INSERT #DummyTrans VALUES ('Fill Log');
        DELETE #DummyTrans;
        SET @Counter = @Counter + 1;
    END
    EXEC (@TruncLog);
END

-- 5. 最終大小報告
DECLARE @FinalSizeMB INT;
SELECT @FinalSizeMB = size * 8 / 1024.0
FROM sys.database_files
WHERE name = @LogicalFileName AND type = 1;
PRINT 'Final Log Size: ' + CAST(@FinalSizeMB AS varchar(10)) + ' MB';

DROP TABLE #DummyTrans;
SET NOCOUNT OFF;

關鍵參數說明

• @LogicalFileName：SELECT name FROM sys.database_files WHERE type=1 獲取，**不是物理路徑**。
• @NewSizeMB：建議收縮至 **日志正常增長週期峰值的 1.5-2 倍**，過小導致頻繁自動增長 (VLF 碎片化)。
• BACKUP LOG ... WITH TRUNCATE_ONLY：僅簡單恢復模式下可用，標記非活躍 VLF 可重用。**完整恢復模式必須改為 `BACKUP LOG ... TO DISK` 正規備份後再收縮**。

生產環境安全 SOP (完整恢復模式下)

1. 正規日志備份：BACKUP LOG YourDB TO DISK='\\backup\share\YourDB_log_20240101.trn' WITH COMPRESSION;
2. 驗證備份可用**：RESTORE VERIFYONLY FROM DISK='...';
3. 收縮日志**：DBCC SHRINKFILE (YourDB_log, 512); (目標 512MB)。
4. 立即建立完整備份**：BACKUP DATABASE YourDB TO DISK='...' WITH COMPRESSION; 重新啟動備份鏈。
5. 監控 VLF 數量**：DBCC LOGINFO(YourDB); VLF > 1000 表明過度碎片，需調整自動增長步長 (固定 512MB/1GB，禁用百分比增長)。

為什麼不能直接刪除 .ldf 文件？

• SQL Server 啟動時需掛載 .ldf 進行事務恢復 (REDO/UNDO)，缺失導致庫進入 **SUSPECT/RECOVERY_PENDING** 狀態。
• 日志是 **WAL (Write-Ahead Logging)** 核心組件，保證 ACID 持久性；刪除等同破壞事務一致性承諾。
• 正確操作：收縮 → 整理 VLF → 監控自動增長策略。

自動化維護建議

• 維護計畫 / SQL Agent Job：每日低峰執行日志備份 + 週末收縮檢查。
• 監控告警：日志文件佔用磁盤 > 70% 或 VLF > 500 觸發告警。
• 自動增長設置：ALTER DATABASE YourDB MODIFY FILE (NAME=YourDB_log, FILEGROWTH=512MB); 固定增長，禁用 10% 百分比增長。

總結

SQL Server 日志清理核心是 **「正規備份 → 截斷非活躍 VLF → 精準收縮 → 重建備份鏈」**。生產環境嚴禁簡單模式繞過備份、嚴禁刪除 .ldf、嚴禁百分比自動增長。將 T-SQL 腳本納入維護計畫，實現日志大小常態化管控。