SQL Server 定時清理歷史資料：Agent 作業 + DATEDIFF 保留 N 天圖文實戰

業務場景

業務表 (如 `OrderLog`、`OperationRecord`) 僅需保留近 N 天 (如 10/30/90 天) 資料，超期自動刪除，控制表體積與備份窗口。

前置條件：啟用 SQL Server Agent

1. 「控制台」→「管理工具」→「服務」→ 找 `SQL Server Agent (MSSQLSERVER)`。
2. 右鍵「屬性」→「啟動類型」選 **自動** → 「啟動」服務。

建立清理作業步驟

1. SSMS → 連線執行個體 → 展開「SQL Server Agent」→ 右鍵「作業」→「新增作業」。
2. 「一般」頁：名稱 `Cleanup_OrderLog_Keep10Days`、類別 `Database Maintenance`、擁有者 `sa`、啟用 ✓。
3. 「步驟」頁 → 「新增」：
   • 步驟名稱：`Delete_Expired_Rows`
   • 類型：`Transact-SQL script (T-SQL)`
   • 資料庫：目標業務資料庫
   • 命令：

     DELETE FROM dbo.OrderLog
     WHERE DATEDIFF(DAY, CAST(SUBSTRING([LogTime], 1, 8) AS DATE), GETDATE()) > 10;

4. 「排程」頁 → 「新增」：
   • 名稱：`Daily_0200`
   • 頻率：每天、每 1 天、每日頻率：一次、發生時間：`02:00:00`。
5. 「通知」頁：勾選「失敗時」→ 寄送郵件給 DBA (需先設 Database Mail + Operator)。
6. 確定建立。

刪除語句深度解析與優化

原始寫法 (字串轉換、函數在欄位上 → 索引失效)

DELETE FROM dbo.OrderLog
WHERE DATEDIFF(DAY, CAST(SUBSTRING([LogTime], 1, 8) AS DATE), GETDATE()) > 10;

• `LogTime` 若為 `CHAR(14)` 格式 `YYYYMMDDHHMMSS`，`SUBSTRING(...,1,8)` 取日期部份。
• **缺點**：欄位套函數 → **無法使用索引** → 全表掃描，大表極慢且產生大量鎖/記錄檔。

優化寫法一：SARGable 範圍查詢 (推薦)

DECLARE @CutoffDate DATE = DATEADD(DAY, -10, CAST(GETDATE() AS DATE));
DELETE FROM dbo.OrderLog
WHERE CAST(SUBSTRING([LogTime], 1, 8) AS DATE) < @CutoffDate;

• 右邊為常數/變數，左邊仍有函數，但可配合**計算欄位 + 索引** 達到 Seek。

優化寫法二：計算欄位持久化 + 索引 (最佳)

-- 1. 新增計算欄位
ALTER TABLE dbo.OrderLog ADD LogDate AS CAST(SUBSTRING([LogTime], 1, 8) AS DATE) PERSISTED;

-- 2. 建立索引
CREATE INDEX IX_OrderLog_LogDate ON dbo.OrderLog (LogDate);

-- 3. 刪除語句 (純粹範圍查詢，Index Seek)
DELETE FROM dbo.OrderLog WHERE LogDate < DATEADD(DAY, -10, CAST(GETDATE() AS DATE));

優化寫法三：分批刪除 (避免長交易、記錄檔暴漲、鎖升級)

DECLARE @BatchSize INT = 5000, @RowsAffected INT = 1, @CutoffDate DATE = DATEADD(DAY, -10, CAST(GETDATE() AS DATE));

WHILE @RowsAffected > 0
BEGIN
    DELETE TOP (@BatchSize) FROM dbo.OrderLog
    WHERE LogDate < @CutoffDate; -- 需配合計算欄位索引

    SET @RowsAffected = @@ROWCOUNT;
    WAITFOR DELAY '00:00:01'; -- 緩衝 1 秒，釋放鎖、讓記錄檔截斷
END

進階選項：分區表 + SWITCH OUT + TRUNCATE (極大表、秒級清理)

• 按天/月建立分區函數/方案，資料自動落入對應分區。
• 清理時：`ALTER TABLE ... SWITCH PARTITION n TO StagingTable` → `TRUNCATE TABLE StagingTable` (最小記錄、無鎖衝突)。
• 參考「表分區」專題文章實作。

總結

**核心原則**：避免欄位套函數、分批提交、監控記錄檔、備份前驗證可恢復性。