【监控实践】【3.3】监控作业（使用数据库邮件）

我们这里测试就用QQ邮箱直接把查询结果信息发送出来。

前提参考：

【1】Sql server用QQ邮箱发送邮件（相关图形界面文章：数据库邮件功能）

【2】sql server如何把查询结果发邮件出去

【3】sql server 用脚本管理作业

【1】查看有误的作业信息

--查看从昨天0点到现在，运行有误的作业信息

SELECT  j.name                        AS Job_Name        ,
        h.step_id                     AS Step_Id         ,
        h.step_name                   AS Step_Name       ,
        h.message                     AS Message         ,
        h.run_date                    AS Run_Date        ,
       -- h.run_time                    AS Run_Time        ,
        msdb.dbo.agent_datetime(h.run_date, h.run_time) 
                                    AS 'RunDateTime' ,
        CAST(run_duration / 10000 AS VARCHAR(2)) + N'小时'
        + CAST(( run_duration - run_duration / 10000 * 10000 ) / 100 AS VARCHAR(2)) + N'分钟'
        + SUBSTRING(CAST(run_duration AS VARCHAR(10)),
                            LEN(CAST(run_duration AS VARCHAR(10))) - 1, 2)  + N'秒'
        AS run_duration
FROM    msdb.dbo.sysjobhistory h
        LEFT JOIN msdb.dbo.sysjobs j ON h.job_id = j.job_id
        where 1=1--h.step_id!=0
        AND run_date >= CAST(CONVERT(VARCHAR(8), GETDATE()-1, 112) AS INT)
        and h.run_status=0
ORDER BY Job_Name, run_date,h.Step_Id

【2】邮件发送演示

邮件发送演示：（这里没有错误的，我就直接查看正确的了）

use msdb
go
EXEC sp_send_dbmail
    @profile_name = 'dba_profile',
    @recipients = '123@qq.com',
    @subject = '今天的作业错误报表',
@query='
SELECT  j.name                        AS Job_Name        ,
        h.step_id                     AS Step_Id         ,
        h.step_name                   AS Step_Name       ,
        h.message                     AS Message         ,
        h.run_date                    AS Run_Date        ,
       -- h.run_time                    AS Run_Time        ,
        msdb.dbo.agent_datetime(h.run_date, h.run_time) 
                                    AS ''RunDateTime'',
        CAST(run_duration / 10000 AS VARCHAR(2)) + N''小时''
        + CAST(( run_duration - run_duration / 10000 * 10000 ) / 100 AS VARCHAR(2)) + N''分钟''
        + SUBSTRING(CAST(run_duration AS VARCHAR(10)),
                            LEN(CAST(run_duration AS VARCHAR(10))) - 1, 2)  + N''秒''
        AS run_duration
FROM    msdb.dbo.sysjobhistory h
        LEFT JOIN msdb.dbo.sysjobs j ON h.job_id = j.job_id
        where 1=1--h.step_id!=0
        AND run_date >= CAST(CONVERT(VARCHAR(8), GETDATE()-1, 112) AS INT)
        and h.run_status=1 --判断是否运行成功，1为成功 0为失败
ORDER BY Job_Name, run_date,h.Step_Id
'
----------------------
查看结果，发现是乱的，所以我们还是最好使用HTML格式规范它

【3】使用HTML格式发送 

使用html格式发送（这里没有错误的，我就直接用正确的演示了）

use msdb
go

declare @table_HTML nvarchar(max)
set @table_HTML=N'<H1> 作业失败报警</H1><table border=1>'
+N'<tr><th>Job_Name</th><th>Step_Id</th><th>Step_Name</th><th>Message</th><th>Run_Date</th><th>RunDateTime</th>'+
cast(
    (
            SELECT  j.name                        AS td        ,'',
                    h.step_id                     AS td         ,'',
                    h.step_name                   AS td       ,'',
                    h.message                     AS td         ,'',
                    h.run_date                    AS td        ,'',
                   -- h.run_time                    AS Run_Time        ,
                    msdb.dbo.agent_datetime(h.run_date, h.run_time) 
                                                AS 'RunDateTime',
                    CAST(run_duration / 10000 AS VARCHAR(2)) + N'小时'
                    + CAST(( run_duration - run_duration / 10000 * 10000 ) / 100 AS VARCHAR(2)) + N'分钟'
                    + SUBSTRING(CAST(run_duration AS VARCHAR(10)),
                                        LEN(CAST(run_duration AS VARCHAR(10))) - 1, 2)  + N'秒'
                    AS td,''
            FROM    msdb.dbo.sysjobhistory h
                    LEFT JOIN msdb.dbo.sysjobs j ON h.job_id = j.job_id
                    where 1=1--h.step_id!=0
                    AND run_date >= CAST(CONVERT(VARCHAR(8), GETDATE()-1, 112) AS INT)
                    and h.run_status=1 --判断是否运行成功，1为成功 0为失败
            ORDER BY j.name, CAST(run_duration / 10000 AS VARCHAR(2)) + N'小时'
                    + CAST(( run_duration - run_duration / 10000 * 10000 ) / 100 AS VARCHAR(2)) + N'分钟'
                    + SUBSTRING(CAST(run_duration AS VARCHAR(10)),
                                        LEN(CAST(run_duration AS VARCHAR(10))) - 1, 2)  + N'秒',h.Step_Id
            for xml path ('tr')
    ) as nvarchar(max)    
)+'</table>'



EXEC sp_send_dbmail
    @profile_name = 'dba_profile',
    @recipients = '123@qq.com',
    @subject = '今天的作业错误报表',
    @body_format='html',
    @body= @table_HTML

 

还可以使用其他方式把查询结果保存到文件，以附件的形式发送，这里不在演示，具体可以参考一下 ：

【2】sql server如何把查询结果发邮件出去