SQL Server Alert发送告警邮件少了的由来

近些年忽然意识我们安排在数据库方面的告警(Alert),当错误日志里面出现谬误时,并不是每个错误日志都会发送邮件出来。如下所示,设置了报警“SQL
Server Severity 伊芙(Eve)nt 14”

 

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 name FROM msdb.dbo.sysalerts WHERE name= N’SQL Server Severity Event
14′)

 

    EXEC msdb.dbo.sp_delete_alert @name=N’SQL Server Severity Event
14′

GO

 

 

EXEC
msdb.dbo.sp_add_alert @name=N’SQL Server Severity Event
14′,

       
@message_id=0,

       
@severity=14,

       
@enabled=1,

       
@delay_between_responses=60,

       
@include_event_description_in=1,

       
@category_name=N’DBA_MONITORING’,

       
@job_id=N’00000000-0000-0000-0000-000000000000′

GO

 

 

EXEC
msdb.dbo.sp_add_notification @alert_name=N’SQL Server Severity Event
14′,
@operator_name=N’YourSQLDba_Operator’, @notification_method
= 1

GO

 

 

接下来自己尝试用sa登录(sa已经被剥夺)了三次,然则自己只收取了一封邮件。特意查看了一下sp_add_alert的合法文档,才精晓现身这些原因,是因为参数@delay_between_responses的值设置缘故,通过设置该值,可以预防在在长时间内重新发送一些不需要的电子邮件。如上所示,一秒钟内,虽然不当日志里面出现了汪洋类似的谬误,也只会发送一封报警邮件。其实只是为着削减发送告警的效率,假如您想当错误日志里出现那么些级其它告警时,都不可能不发送告警邮件,可以将其值设置为0。不过有时,尽管设置为0,你会接到多样的邮件。其实这些小问题,只是因为原先尚未特别注意那么些参数而已。存粹属于尚未彻底掌握这多少个功能罢了。

 

 

 

图片 1

 

 

 

@delay_between_responses
=

] delay_between_responses

The
wait period, in seconds, between responses to the alert.
delay_between_responsesis int, with a default of 0, which
means there is no waiting between responses (each occurrence of the
alert generates a response). The response can be in either or both of
these forms:

·        
 

·        
One
or more notifications sent through e-mail or pager.

·        
 

·        
A
job to execute.

·        
By
setting this value, it is possible to prevent, for example, unwanted
e-mail messages from being sent when an alert repeatedly occurs in a
short period of time.

 

@delay_between_responses
=] delay_between_responses

警报响应期间的等候时间
(以秒为单位)。delay_between_responsesis int, 默认值为 0,
这象征在响应期间一贯不等待
(每一次出现警报都会扭转响应)。响应得以是在偏下二种样式中的一个,
或者都是:

因而电子邮件或寻呼机发送的一个或两个通告。

要执行的课业。

由此设置此值,
可以避免例如, 在长期内再次爆发警报时发送不需要的电子邮件。

 

 

参考资料:

 

https://docs.microsoft.com/zh-cn/sql/relational-databases/system-stored-procedures/sp-add-alert-transact-sql

相关文章