萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> MS SQL 資料庫

SQL Server 定期清理資料表只保留 N 天資料的作業排程實作

應用場景

針對大量寫入的日誌、流水帳等資料表,需自動保留最近 N 天資料,定期清除舊資料以控制資料庫體積。

前置作業:啟用 SQL Server Agent

  1. 控制台 → 系統管理工具 → 服務。
  2. 找到 SQL Server Agent (MSSQLSERVER),右鍵 屬性 → 啟動類型:自動 → 啟動。

SQL Server Agent 服務設定

建立清理作業

  1. 開啟 SSMS,連線至執行個體,展開 SQL Server Agent → 右鍵 作業 → 新增作業。
  2. 一般頁面:命名(如 Job_Cleanup_Logs_10Days),擁有者 sa,類別 資料庫維護。

    新增作業 - 一般頁面

  3. 步驟頁面 → 新增:
    • 步驟名稱:Delete_Old_Records
    • 類型:Transact-SQL 指令碼
    • 資料庫:選取目標資料庫
    • 命令:
-- 保留最近 10 天資料,假設時間欄位為 [LogTime] (datetime)
DELETE FROM dbo.LogTable
WHERE DATEDIFF(DAY, [LogTime], GETDATE()) > 10;

進階技巧:若時間欄位為字串(如 '20231015'),需先轉型:

DELETE FROM dbo.LogTable
WHERE DATEDIFF(DAY, CAST(SUBSTRING([TimeStr], 1, 8) AS DATETIME), GETDATE()) > 10;
  1. 排程頁面 → 新增:設定為每日零晨 02:00 執行(低峰期)。
  2. 通知:可設定失敗時寄送 Email 給 DBA。
  3. 確定儲存,右鍵作業 → 啟動作業 測試。

大量資料刪除優化建議

  • 分批刪除(每批 5,000~10,000 筆),避免長時間鎖表與記錄檔暴增。
  • 搭配 CHECKPOINT 或簡易復原模式控制記錄檔成長。
  • 考慮使用 資料分區 配合 SWITCH PARTITION 瞬時切換,效能最佳。
copyright © 萬盛學電腦網 all rights reserved