一、实现JOB任务运行状态的检测

1. 创建操作员

SQL Server Agent → Operators → New Operator

说明：

a. 姓名：操作员的姓名，可以是别名。

b. 电子邮件名称：邮箱的地址。

c. 寻呼值班计划：可根据实际情况设置时间

2. 创建警告

如死锁警告

2.1查询当前警报等级信息

select * from msdb.dbo.syscategories

select * from msdb.dbo.syscategories

select * from msdb.dbo.sysalerts

select * from msdb.dbo.sysalerts

2.2创建

注意修改operator_name操作员名字

USE [msdb]
GO
 
IF NOT EXISTS(SELECT 1 FROM msdb.dbo.syscategories WHERE name='DBA_MONITORING' AND category_class=2)
BEGIN
 
EXEC msdb.dbo.sp_add_category
    @class=N'ALERT',
    @type=N'NONE',
    @name=N'DBA_MONITORING' ;
 
END
GO
 
IF EXISTS(SELECT 1 FROM msdb.dbo.sysalerts WHERE name='SQL Server Dead Lock Detected')
BEGIN
    EXEC msdb.dbo.sp_delete_alert @name=N'SQL Server Dead Lock Detected';
END
GO
 
 
IF NOT EXISTS(SELECT 1 FROM msdb.dbo.sysalerts WHERE name='SQL Server Dead Lock Detected')
BEGIN
EXEC msdb.dbo.sp_add_alert @name=N'SQL Server Dead Lock Detected', 
        @message_id=1205, 
        @severity=0, 
        @enabled=1, 
        @delay_between_responses=0, 
        @include_event_description_in=1, 
        @category_name=N'DBA_MONITORING', 
        @job_id=N'00000000-0000-0000-0000-000000000000'
END
GO
 
IF NOT EXISTS ( SELECT  *
                FROM    msdb.dbo.sysnotifications
                WHERE   alert_id = ( SELECT id
                                     FROM   msdb.dbo.sysalerts
                                     WHERE  name = 'SQL Server Dead Lock Detected'
                                   ) )
    BEGIN
 
        EXEC msdb.dbo.sp_add_notification @alert_name = N'SQL Server Dead Lock Detected',
            @operator_name = N'han', @notification_method = 1;
    END;
GO

USE [msdb]
GO
 
IF NOT EXISTS(SELECT 1 FROM msdb.dbo.syscategories WHERE name='DBA_MONITORING' AND category_class=2)
BEGIN
 
EXEC msdb.dbo.sp_add_category
    @class=N'ALERT',
    @type=N'NONE',
    @name=N'DBA_MONITORING' ;
 
END
GO
 
IF EXISTS(SELECT 1 FROM msdb.dbo.sysalerts WHERE name='SQL Server Dead Lock Detected')
BEGIN
    EXEC msdb.dbo.sp_delete_alert @name=N'SQL Server Dead Lock Detected';
END
GO
 
 
IF NOT EXISTS(SELECT 1 FROM msdb.dbo.sysalerts WHERE name='SQL Server Dead Lock Detected')
BEGIN
EXEC msdb.dbo.sp_add_alert @name=N'SQL Server Dead Lock Detected', 
        @message_id=1205, 
        @severity=0, 
        @enabled=1, 
        @delay_between_responses=0, 
        @include_event_description_in=1, 
        @category_name=N'DBA_MONITORING', 
        @job_id=N'00000000-0000-0000-0000-000000000000'
END
GO
 
IF NOT EXISTS ( SELECT  *
                FROM    msdb.dbo.sysnotifications
                WHERE   alert_id = ( SELECT id
                                     FROM   msdb.dbo.sysalerts
                                     WHERE  name = 'SQL Server Dead Lock Detected'
                                   ) )
    BEGIN
 
        EXEC msdb.dbo.sp_add_notification @alert_name = N'SQL Server Dead Lock Detected',
            @operator_name = N'han', @notification_method = 1;
    END;
GO

• 效果

3.设置SQL Server代理

SQL Server 代理 -- 右键 -- 属性

补充：其他选项页均保留默认设置

4. 配置job

发送Database Log Space 邮件

1.创建job

SQL Server Agent → Job → New Job

• General 选项页配置如下

• 配置 Step 选项页，点击 New

只修改常规

EXEC sp_send_dbmail
    @profile_name = 'SendEmailProfile', -- 修改
    @recipients = 'xxx@qq.com', -- 修改自己接收者邮箱
    @subject = 'Data Log Space',@query='dbcc sqlperf(logspace)'

EXEC sp_send_dbmail
    @profile_name = 'SendEmailProfile', -- 修改
    @recipients = 'xxx@qq.com', -- 修改自己接收者邮箱
    @subject = 'Data Log Space',@query='dbcc sqlperf(logspace)'

高级不动

配置 Scheduler 选项页，点击 New

根据实际需求配置完成后，点击 OK

本例配置的是 每天每 2h 发一次邮件

2.测试job