SQL Server 大量資料刪除實戰：從日誌暴增災難到分批刪除優雅方案

真實事故：日誌暴增 45GB 癱瘓磁碟

某系統每天透過 Job 刪除 90 天前資料，單表超過 1 億筆。原刪除腳本使用子查詢與聯接，單次執行觸發大量日誌寫入，導致日誌磁碟 (D:) 可用空間從 95GB 驟降至 40GB 以下觸發告警。資料檔 (E:) 因刪除與新增平衡未明顯變化，但日誌檔從數百 MB 暴增至 45GB+，極度危險。

DELETE vs TRUNCATE：核心差異

基於聯接的 DELETE 寫法

T-SQL 支援非標準的 FROM ... JOIN ... WHERE 語法，邏輯處理順序：FROM → WHERE → DELETE。標準 SQL 寫法可用 EXISTS 子查詢達到同樣效果，筆者偏好標準寫法以利可攜性。

分批刪除：解決日誌暴增的銀彈

核心原則：小批次、高頻率、即時提交，使日誌空間持續重用，避免單一長交易撐爆日誌。

方案 A：有主鍵 ID — 按 ID 迴圈刪除

-- 2012-03-30 因大量刪除日誌問題，改為分批刪除
DECLARE @MINID INT, @N INT;

-- 1. 取出需刪除的 90 天前記錄 ID 存入暫存表
SELECT BinTestID INTO #S5ID
FROM dbo.S5_BinTest_Info
WHERE TS < DATEADD(dd, -90, GETDATE());

-- 2. 以需刪除 ID 總數為迴圈變數 (ID 可能不連續)
SELECT @N = COUNT(1) FROM #S5ID;

WHILE (@N > 0)
BEGIN
    -- 一次刪除一個 ID 對應的所有明細與主檔資料
    SELECT @MINID = MIN(BinTestID) FROM #S5ID;
    DELETE FROM dbo.S5_BinTest_Detail WHERE BinTestID = @MINID;
    DELETE FROM dbo.S5_BinTest_Info WHERE BinTestID = @MINID;

    -- 從暫存表移除已處理 ID
    DELETE #S5ID WHERE BinTestID = @MINID;
    SELECT @N = COUNT(1) FROM #S5ID;
END
DROP TABLE #S5ID;

方案 B：無主鍵 — 使用 TOP 子句分批

WHILE (1=1)
BEGIN
    DELETE TOP (5000) FROM dbo.BigTable WHERE CreateDate < DATEADD(dd, -90, GETDATE());
    IF @@ROWCOUNT = 0 BREAK;
    -- 可選：WAITFOR DELAY '00:00:01' 降低衝擊
END

實測效果

採用分批刪除後，日誌檔基本不再增長，因每次刪除極少量資料，日誌空間即時釋放重用。原本需數小時且高風險的操作，變為平穩、可控、可中斷的常態化維護任務。

給 DBA 的提醒

刪除大量資料時，務必同時考量語法正確性、業務條件與日誌空間承載力。分批刪除、調整復原模式 (簡易/大量記錄)、預先擴展日誌檔、監控日誌使用率，是生產環境必備的基本功。