Why does SQL Server Alert send fewer alarm emails?

Why does SQL Server Alert send fewer alarm emails?

Recently, we suddenly found the alarm (Alert) deployed on the database. When an error occurs in the error log, not every error log will be sent by email. As shown in the following figure, the alarm "SQL Server Severity Event 14" is set"

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' ; ENDGO 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 = 1GO

Then I tried to log on with sa three times (sa has been disabled), but I only received one email. I checked the official sp_add_alert document to find out the cause. This is because the value of @ delay_between_responses is set, it can prevent unnecessary emails from being repeatedly sent in a short period of time. As shown above, within one minute, even if a large number of similar errors appear in the error log, only one alarm email is sent. In fact, it is only to reduce the frequency of sending alarms. If you want to send alarms at this level in the error log, you must send an alarm email. You can set the value to 0. However, if it is set to 0, you will receive overwhelming emails. In fact, this small problem is only because we did not pay attention to this parameter before. These functions are not fully understood.

Wait time (in seconds) between alert responses ). Delay_between_responsesis int. The default value is 0, which means no waiting between responses (a response is generated every time an alarm occurs ). The response can be either of the following two forms, or both:
One or more notifications sent by Email or Pager.
The job to be executed.
You can set this value to prevent unwanted emails from being sent when an alert is repeated for a short period of time.

References:

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

The above is all the content of this article. I hope it will be helpful for your learning and support for helping customers.