sqlserver作业失败提醒
业务侧反馈数据统计有问题,登录服务器才发现作业执行失败。为避免晚发现的情况影响到业务体验,使用脚本触发邮件报警。
一下脚本可以放到sql作业中执行
DECLARE @mailBody NVARCHAR(MAX) --邮件内容
DECLARE @replicationInfo NVARCHAR(max) --邮件抓取内容
DECLARE @errnum int --阈值 如果超过阈值,触发报警
--获取30分钟内作业失败的记录
SELECT * into #tmp_sysjob from (select job_id,step_name,message,
cast(
(
cast( LEFT ( run_date, 4 ) AS VARCHAR ) + '-' + SUBSTRING( cast( run_date AS VARCHAR ), 5, 2 ) + '-' + cast( RIGHT ( run_date, 2 ) AS VARCHAR )
) + ' ' + (
cast( LEFT ( RIGHT ( '000000' + CAST( run_time AS nvarchar ( 50 ) ), 6 ), 2 ) AS VARCHAR ) + ':' + SUBSTRING( cast( RIGHT ( '000000' + CAST( run_time AS nvarchar ( 50 ) ), 6 ) AS VARCHAR ), 3, 2 ) + ':' +
cast( RIGHT ( RIGHT ( '000000' + CAST( run_time AS nvarchar ( 50 ) ), 6 ), 2 ) AS VARCHAR )
) AS datetime ) datetime
FROM msdb.dbo.sysjobhistory where message like'%该作业失败%' and
cast(
(
cast( LEFT ( run_date, 4 ) AS VARCHAR ) + '-' + SUBSTRING( cast( run_date AS VARCHAR ), 5, 2 ) + '-' + cast( RIGHT ( run_date, 2 ) AS VARCHAR )
) + ' ' + (
cast( LEFT ( RIGHT ( '000000' + CAST( run_time AS nvarchar ( 50 ) ), 6 ), 2 ) AS VARCHAR ) + ':' + SUBSTRING( cast( RIGHT ( '000000' + CAST( run_time AS nvarchar ( 50 ) ), 6 ) AS VARCHAR ), 3, 2 ) + ':' +
cast( RIGHT ( RIGHT ( '000000' + CAST( run_time AS nvarchar ( 50 ) ), 6 ), 2 ) AS VARCHAR )
) AS datetime )>=dateadd(minute,-30,GETDATE()) --cast都是日期+时间转datetime,取当前30分钟以内作业失败的数量
) tmp;
--判断条件
select @errnum =(SELECT count(1) FROM #tmp_sysjob)
-- select * from msdb.dbo.sysjobs a(nolock)
IF @errnum>0 --如果作业失败>0,出发报警
print @errnum
BEGIN --一下是报警发送的信息
SET @replicationInfo =
N'<H3>SQLSERVER代理作业错误</H3>'
+ N'<table border="1">'
+N'<th>[任务名称]</th>
<th>[作业结果]</th>
<th>[详细]</th>
<th>[作业时间]</th>'
+ Cast((SELECT
a.NAME AS 'td', '',
b.step_name AS 'td', '',
b.message AS 'td', '',
b.datetime AS 'td'
FROM
msdb.dbo.sysjobs a(nolock),
#tmp_sysjob b
WHERE
a.job_id = b.job_id
FOR xml path('tr'), elements) AS NVARCHAR(max)) + N'</table>';
SET @mailBody=isnull( @replicationInfo,'')
EXEC [msdb].[dbo].[sp_send_dbmail]
@profile_name = 'jiankong', -- 报警名称
@recipients = '……', -- varchar(max) --收件人 -- <<<修改>>>
@subject = N'SQLSERVER代理作业错误', -- nvarchar(255) 标题
@body_format = 'HTML', -- varchar(20) 正文格式可选值:text html
@body = @mailBody
END
drop table #tmp_sysjob