如何配置SQL Server作业警报：作业停跑超30分钟触发邮件通知

原脚本的问题 #

• @profile_name 字符串未闭合（缺少结尾单引号）
• 邮件主题、正文的赋值语句写在发送邮件的sp_send_dbmail之后，导致发送时变量为空
• 未定义@start_time_local变量，执行会直接报错
• 没有核心的作业状态检查逻辑，单纯延迟后发邮件根本无法实现“检测作业停止超30分钟”的需求

正确实现方案 #

步骤1：确认数据库邮件配置 #

确保SQL Server已配置好可用的数据库邮件账户和配置文件（对应脚本里的@profile_name）。

步骤2：检测超时作业的脚本 #

以下脚本会自动筛选出所有停止运行超过30分钟的SQL Server代理作业，收集相关信息后发送邮件：

DECLARE @profile_name VARCHAR(MAX) = 'Default Profile'; -- 修正：补全单引号
DECLARE @email_to_address VARCHAR(MAX) = 'Domain@myemail.com';
DECLARE @email_subject VARCHAR(MAX);
DECLARE @email_body VARCHAR(MAX);
DECLARE @server_name VARCHAR(MAX) = ISNULL(@@SERVERNAME, CAST(SERVERPROPERTY('ServerName') AS VARCHAR(MAX)));

-- 临时表存储超时作业信息
DECLARE @timeout_jobs TABLE (
    JobName NVARCHAR(128),
    LastRunEndTime DATETIME,
    TimeElapsedMinutes INT
);

-- 筛选停止超30分钟的作业
INSERT INTO @timeout_jobs
SELECT 
    j.name AS JobName,
    CONVERT(DATETIME, RTRIM(ja.run_date)) + CONVERT(DATETIME, STUFF(STUFF(RIGHT('000000' + RTRIM(ja.run_time), 6), 3, 0, ':'), 6, 0, ':')) AS LastRunEndTime,
    DATEDIFF(MINUTE, CONVERT(DATETIME, RTRIM(ja.run_date)) + CONVERT(DATETIME, STUFF(STUFF(RIGHT('000000' + RTRIM(ja.run_time), 6), 3, 0, ':'), 6, 0, ':')), GETDATE()) AS TimeElapsedMinutes
FROM 
    msdb.dbo.sysjobs j
JOIN 
    msdb.dbo.sysjobhistory ja ON j.job_id = ja.job_id
WHERE 
    ja.step_id = 0 -- 取作业整体运行记录（非单步骤）
    AND ja.run_status = 1 -- 作业成功完成（如果要包含失败的，可去掉此条件）
    AND DATEDIFF(MINUTE, CONVERT(DATETIME, RTRIM(ja.run_date)) + CONVERT(DATETIME, STUFF(STUFF(RIGHT('000000' + RTRIM(ja.run_time), 6), 3, 0, ':'), 6, 0, ':')), GETDATE()) > 30
GROUP BY 
    j.name, ja.run_date, ja.run_time
HAVING 
    CONVERT(DATETIME, RTRIM(ja.run_date)) + CONVERT(DATETIME, STUFF(STUFF(RIGHT('000000' + RTRIM(ja.run_time), 6), 3, 0, ':'), 6, 0, ':')) = MAX(CONVERT(DATETIME, RTRIM(ja.run_date)) + CONVERT(DATETIME, STUFF(STUFF(RIGHT('000000' + RTRIM(ja.run_time), 6), 3, 0, ':'), 6, 0, ':')));

-- 存在超时作业则发送邮件
IF EXISTS(SELECT 1 FROM @timeout_jobs)
BEGIN
    SET @email_subject = '作业超时警报: ' + @server_name;

    -- 构建HTML格式邮件正文
    SET @email_body = '<html><body>';
    SET @email_body += '<h3>以下作业已停止运行超过30分钟:</h3>';
    SET @email_body += '<table border="1" cellpadding="4">';
    SET @email_body += '<tr><th>作业名称</th><th>上次结束时间</th><th>已超时分钟数</th></tr>';

    -- 插入作业数据到表格
    SELECT 
        @email_body += '<tr><td>' + JobName + '</td><td>' + CONVERT(VARCHAR, LastRunEndTime, 120) + '</td><td>' + CAST(TimeElapsedMinutes AS VARCHAR) + '</td></tr>'
    FROM @timeout_jobs;

    SET @email_body += '</table></body></html>';

    -- 发送邮件
    EXEC msdb.dbo.sp_send_dbmail
        @profile_name = @profile_name,
        @recipients = @email_to_address,
        @subject = @email_subject,
        @body_format = 'html',
        @body = @email_body;
END

步骤3：创建定时监控作业 #

1. 打开SQL Server代理，新建作业
2. 添加作业步骤：类型选“Transact-SQL脚本(T-SQL)”，数据库选msdb，粘贴上述脚本
3. 设置作业计划：比如每15分钟执行一次，确保及时检测超时情况
4. 保存作业，确认SQL Server代理服务处于运行状态

关键说明 #

• 通过查询msdb系统表获取作业历史运行记录，精准筛选停止超30分钟的作业
• 使用HTML格式邮件，让作业信息更直观清晰
• 定期执行监控作业，替代原脚本中不合理的单次延迟逻辑

内容的提问来源于stack exchange，提问作者KingDomain