sqlserver作业失败提醒

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