萬盛學電腦網

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

SQL Server 2012/2014/2016/2019 建立定時作業圖文教學:Agent 作業、步驟、排程與通知

SQL Server Agent 作業是自動化維護(備份、索引重建、統計更新、ETL、報表生成)的核心工具。本文以圖文並茂方式,手把手教你建立完整定時作業:從啟用 Agent、新建作業、編寫步驟(T-SQL/SSIS/CmdExec)、設定排程、配置通知到驗證執行歷史。

前置條件

  • SQL Server Agent 服務啟動類型設為 自動,且狀態為 正在運行
  • 登入帳號具備 sysadmin 固定伺服器角色
  • 資料庫郵件 已配置(需郵件通知時)

步驟 1:展開 Agent → 右鍵「作業」→「新增作業」

展開 Agent 作業節點

步驟 2:一般頁面 - 基本資訊

新增作業對話框

  • 名稱:命名規範,如 Job_Daily_Backup_UserDB
  • 擁有者:建議 sa 或專用服務帳號
  • 類別:Database Maintenance、Report Server 等,便於篩選
  • ✅ 已啟用:必須勾選,否則排程不觸發
  • 描述:用途、負責人、變更記錄

步驟 3:步驟頁面 - 新增執行步驟

新增作業步驟

  • 步驟名稱:如 Step_Backup_Full
  • 類型:
    • Transact-SQL 指令碼(最常用)
    • SSIS 套件、PowerShell、CmdExec、Replication 等
  • 資料庫:指定執行上下文資料庫(如 master 或業務庫)
  • 命令:輸入 T-SQL,直接呼叫預存程序:
    EXEC dbo.usp_BackupAllUserDatabases @BackupPath = 'D:\Backup', @Type = 'FULL';
  • 點擊 剖析 驗證語法 剖析成功
  • 進階:設定成功/失敗動作(轉下一步、結束作業、重試次數、重試間隔)

步驟 4:排程頁面 - 設定觸發時間

新增作業排程

排程屬性

  • 排程類型:週期性(每日/每週/每月)、一次、啟動 CPU 閒置時
  • 頻率:每日 每 1 天、每週 週一至週五、每月 第 1 天
  • 每日頻率:發生一次(指定時間)、每隔 N 小時、每隔 N 分鐘
  • 持續期間:生效起迄日期
  • 範例:每天凌晨 02:00 執行
    • 頻率:每日,每 1 天
    • 每日頻率:發生一次,02:00:00

步驟 5:通知頁面(可選)

作業通知設定

  • 郵件/尋呼/Net Send 通知操作員
  • 條件:作業成功、失敗、完成時
  • 前提:已建立 操作員 並配置 資料庫郵件

步驟 6:目標伺服器(多實例環境)

目標伺服器

  • 本地伺服器(預設)
  • 多伺服器管理(MSX/TSX)時指定目標 TSX

步驟 7:完成與驗證

作業建立完成

  • 右鍵作業 → 啟動作業 手動測試
  • 右鍵作業 → 檢視歷程記錄 查看執行結果、持續時間、輸出訊息 步驟命令

T-SQL 腳本化建立作業(自動化部署)

USE msdb;
GO
EXEC dbo.sp_add_job
    @job_name = N'Job_Daily_IndexOptimize',
    @enabled = 1,
    @description = N'每日凌晨重建碎片索引',
    @category_name = N'Database Maintenance',
    @owner_login_name = N'sa';
GO

EXEC dbo.sp_add_jobstep
    @job_name = N'Job_Daily_IndexOptimize',
    @step_name = N'Step_Reindex',
    @subsystem = N'TSQL',
    @command = N'EXEC dbo.usp_IndexOptimize @Databases = ''USER_DATABASES'', @FragmentationHigh = ''INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE'';',
    @database_name = N'master',
    @on_success_action = 1,  -- 成功結束作業
    @on_fail_action = 2,     -- 失敗結束作業
    @retry_attempts = 2,
    @retry_interval = 5;
GO

EXEC dbo.sp_add_schedule
    @schedule_name = N'Sched_Daily_0200',
    @freq_type = 4,           -- 每日
    @freq_interval = 1,
    @active_start_time = 20000; -- 02:00:00
GO

EXEC dbo.sp_attach_schedule
    @job_name = N'Job_Daily_IndexOptimize',
    @schedule_name = N'Sched_Daily_0200';
GO

EXEC dbo.sp_add_jobserver
    @job_name = N'Job_Daily_IndexOptimize';
GO

常見坑與最佳實踐

坑點後果對策
Agent 服務未啟動作業永不執行設為自動啟動、監控服務狀態
未勾選「已啟用」排程不觸發建立腳本強制 @enabled=1
步驟命令寫完整 SQL 而非呼叫 SP維護困難、無法版控業務邏輯封裝為 SP,作業只 CALL
排程重疊、長跑作業並發資源爭用、死鎖錯開時段、設定「單一執行」鎖邏輯
無通知/無日誌保留失敗不知情配置郵件通知、sp_purge_jobhistory 定期清理

定時作業是資料庫自動化運維的骨架。掌握 GUI 操作是入門,**掌握 T-SQL 腳本化部署才是進階**——將作業定義納入版控、CI/CD 流水線,實現「基礎設施即代碼」,讓每次變更可追溯、可回滾、可審計。

copyright © 萬盛學電腦網 all rights reserved