SQL Server Agent 作業排程完整指南：建立、監控、遷移與錯誤 14274 解決

建立排程作業標準流程

步驟 1：新建作業 (General)

• 名稱：具備業務辨識度 (如 Daily_Index_Maintenance)
• 啟用：勾選
• 類別：分類管理 (可自建：維護、備份、ETL、報表)
• 擁有者：具備執行權限的登入 (建議專用服務帳戶)
• 描述：用途、相依性、聯絡人、SLA

步驟 2：定義步驟 (Steps)

-- 步驟類型選擇
-- 1. Transact-SQL 指令碼 (TSQL) - 最常用
-- 2. CmdExec - 執行 .bat/.exe
-- 3. PowerShell - 現代化腳本
-- 4. SSIS 套件 - ETL 流程
-- 5. 複寫 - 發佈/訂閱同步

TSQL 步驟範例

-- 簡單陳述式
BACKUP DATABASE SalesDB TO DISK = 'D:\Backup\SalesDB_Full.bak' WITH INIT, COMPRESSION;

-- 執行儲存程序
EXEC dbo.usp_IndexOptimize @Databases = 'USER_DATABASES', @FragmentationHigh = 'REBUILD';

-- 多步驟依賴：步驟 1 成功 → 步驟 2，失敗 → 步驟 3 (通知)

步驟 3：設定排程

步驟 4：通知與記錄

• 作業失敗 → 寫入 Windows 應用程式事件日誌 (預設)
• 可擴充：E-mail 通知 (需 Database Mail)、網路訊息、分頁
• 歷程記錄保留策略見下文

歷程記錄保留策略配置

-- 企業管理員 → SQL Server Agent 右鍵 → 屬性 → 作業系統
-- 或 T-SQL 查詢/修改 msdb 系統表

兩者有制約關係：單一作業上限 × 作業數量 ≤ 總上限。依實際作業數量調整。

錯誤 14274 解決：伺服器重命名後作業無法修改

原因：msdb.dbo.sysjobs.originating_server 仍存舊伺服器名稱。

修復腳本

USE msdb;
GO

-- 查詢受影響作業
SELECT job_id, name, originating_server 
FROM sysjobs 
WHERE originating_server <> @@SERVERNAME;

-- 更新為目前伺服器名稱
UPDATE sysjobs 
SET originating_server = @@SERVERNAME
WHERE originating_server <> @@SERVERNAME;

或針對單一作業：

UPDATE sysjobs 
SET originating_server = 'NewServerName'
WHERE job_id = 'B23BBEBE-A3C1-4874-A4AB-0E2B7CD01E14';

作業遷移：跨伺服器部署

匯出腳本

1. 企業管理員 → SQL Server Agent → 作業右鍵 → 「所有工作」→ 「產生 SQL 指令碼」
2. 儲存為 .sql 檔案

目標伺服器部署前檢查

• 建立相同登入/服務帳戶
• 修正腳本中不存在的使用者參考
• 調整檔案路徑 (如 D:\Backup\ → F:\Backup\)
• 移除命令中的 GO 批次分隔符 (非 SSMS 執行時)

必要條件檢查清單

• ✅ SQLSERVERAGENT 服務正在執行
• ✅ Agent 服務啟動帳戶 = SQL Server 服務啟動帳戶 (或具同等權限)
• ✅ msdb 資料庫線上且健康
• ✅ 作業擁有者登入存在且啟用

作業自動化是 DBA 從「手工操作」進化到「流程治理」的關鍵。善用分類、命名規範、歷程保留策略，讓自動化可觀測、可維護。