如何创建和配置SQL Server代理警报

介绍 (Introduction)

If you have ever wanted to run a job dependent on a certain performance condition, then this article is for you.

如果您曾经想根据特定的性能条件运行作业，那么本文适合您。

Usually SQL Server agent jobs are configured to run on a schedule. But what if instead of a schedule you want a job to be executed when a certain performance threshold is exceeded?

通常，SQL Server代理作业被配置为按计划运行。 但是，如果您希望在超过特定性能阈值时执行作业而不是计划，该怎么办？

SQL Server Agent Alerts are what you need.

SQL Server代理警报是您所需要的。

警报如何帮助我？ (How can alerts help me?)

You are probably asking why you need to configure alerts when you want a job to be executed? After all you want this to happen without human intervention. The truth is that SQL Server Agent Alerts can do more than just send alerts.

您可能会问为什么要执行作业时为什么需要配置警报？ 毕竟，您希望做到这一点而无需人工干预。 事实是，SQL Server代理警报可以做的不仅仅是发送警报。

In fact, it has the option to either send an alert or to execute an existing job or both.

实际上，它可以选择发送警报或执行现有作业，或两者兼而有之。

警报类型 (Alert types)

SQL Server Agent supports 3 types of alerts:

SQL Server代理支持3种类型的警报：

• SQL Server event alerts
  This fires an alert when a specific SQL Server even occurs, such as when a specific error number occurs, or a specific severity level.

  SQL Server事件警报
  当甚至发生特定SQL Server（例如，发生特定的错误号或特定的严重性级别）时，这将触发警报。

• SQL Server performance event alerts
  This fires an alert when a performance counter reaches the specified threshold, such as if the average wait time for the Network IO wait statistic exceeds a certain value.

  SQL Server性能事件警报
  当性能计数器达到指定的阈值时（例如，网络IO等待统计信息的平均等待时间超过某个值），这将触发警报。

• WMI event alerts
  This fires an event in response to a Windows Management Instrumentation (WMI) event, such as when a new file appears in a specific folder.

  WMI事件警报
  这会响应Windows Management Instrumentation（WMI）事件而触发事件，例如，当新文件出现在特定文件夹中时。

安全 (Security)

In order to create an alert, you have to be a member of the sysadmin group, since the procedure sp_add_alert can only be executed by a member of sysadmin.

为了创建警报，您必须是sysadmin组的成员，因为过程sp_add_alert只能由sysadmin成员执行。

创建一个SQL Server代理警报 (Creating a SQL Server Agent Alert)

This functionality can be accessed by going to SQL Server Agent in SQL Server Management Studio, and right clicking on the on the Alerts node.

可以通过转到SQL Server Management Studio中SQL Server代理，然后右键单击“警报”节点上的来访问此功能。

The New Alert dialog contains 4 pages:

“新建警报”对话框包含4页：

• General
  This is where you configure the basic details of the Alert such as the name and alert type.

  一般
  在此配置警报的基本详细信息，例如名称和警报类型。

• Response
  This is where you configure what should happen in response to the event.

  响应
  在此配置响应事件应发生的情况。

• Options
  Allows you to specify what should be included in the messages, and how long the delay between the responses should be.

  选件
  允许您指定消息中应包含的内容以及响应之间的延迟应有多长时间。

• History
  Displays the history of the alerts such as when the last one occurred, if there was a response , how many times it has occurred and allows you to reset the count.

  历史
  显示警报的历史记录，例如上一次发生警报的时间，是否发生响应，发生警报的次数以及允许您重置计数。

创建一个SQL Server事件警报 (Creating a SQL Server event alert)

Creating a SQL Server event alert is useful in cases where you want to perform an action in response to a specific error or error severity.

在要响应特定错误或错误严重性而执行操作的情况下，创建SQL Server事件警报非常有用。

In example: Let’s say an error with a severity level of 23 occurs, indicating that there may be a hardware or software problem which may have left your database in an inconsistent state. You may want to run DBCC CHECKDB to determine the extent of the problem.

例如：发生严重级别为23的错误，表明可能存在硬件或软件问题，可能使数据库处于不一致状态。 您可能需要运行DBCC CHECKDB以确定问题的程度。

To do this click on New Alert…

为此，请单击“新警报”。

The New Alert Dialog appears

出现“新警报”对话框

Update the following details for the alert:

更新警报的以下详细信息：

• Name

  名称

• Type
  In this case, we will user a SQL Server event alert

  类型
  在这种情况下，我们将使用SQL Server事件警报

• Database

  数据库

• 
  In this case, we are using Severity and selecting severity level 23. Alternatively, you can specify an Error number or a specific word or phrase contained within the error message.
  在这种情况下，我们将使用严重性并选择严重性级别23。或者，您可以指定错误号或错误消息中包含的特定单词或短语。

Once the Alert details are configured we will configure what should happen when this event occurs. We do that by going to the Response page.

配置了警报详细信息后，我们将配置此事件发生时应发生的情况。 为此，请转到“响应”页面。

There are two potential options for a response:

响应有两种可能的选择：

1. Execute a job

   执行工作

2. Notify an operator or operators

   通知一个或多个操作员

These options are not mutually exclusive. You are able to execute a job and notify an operator if you wish to do so.

这些选项不是互斥的。 您可以执行作业，并在需要时通知操作员。

In this case, we will execute a job to check the consistency of the database in question.

在这种情况下，我们将执行一个作业来检查相关数据库的一致性。

创建SQL Server性能状况警报 (Creating a SQL Server Performance Condition alert)

This alert type will be used if you want to create an alert based on a specific performance condition. This could be something like if the Page Life Expectancy hits a certain value or if the Queue Length becomes too long.

如果要基于特定的性能条件创建警报，将使用此警报类型。 如果“页面寿命期望”达到某个值，或者“队列长度”过长，则可能会出现这种情况。

In this example, I will setup an alert which will fire when the transaction log used space rises above 90%, which will then execute a job which will attempt to back up the log. If you want to, you can always include some functionality to check that the log file got truncated after the backup and notify someone in case there is a specific reason as to why the log is not getting truncated. The details and complexity of the logic is up to you.

在此示例中，我将设置一个警报，当事务日志的已用空间超过90％时将触发该警报，然后执行将尝试备份日志的作业。 如果愿意，您始终可以包括一些功能，以在备份后检查日志文件是否被截断，并在某些特定原因导致日志未被截断的情况下通知某人。 逻辑的细节和复杂性取决于您。

Update the following details for the alert:

更新警报的以下详细信息：

• Object

  目的

• Type
  In this case, we will user a SQL Server performance condition alert

  类型
  在这种情况下，我们将使用SQL Server性能状况警报

• Counter
  The performance counter you are interested in, such as the Percentage log used.

  计数器
  您感兴趣的性能计数器，例如使用的百分比日志。

• Instance
  Since we specified database as the object, we should select the database name here

  实例
  由于我们将数据库指定为对象，因此我们应在此处选择数据库名称

• Alert if counter
  Specify the counter threshold, if it reaches a specific value.

  计数器提醒
  指定计数器阈值（如果达到特定值）。

创建一个WMI事件警报 (Create a WMI event alert)

The WMI event type allows you to configure an alert if a specific event occurred in Windows. In this example, I will create an event which fires when a file is dropped into a specific folder.

WMI事件类型允许您配置Windows中发生特定事件的警报。 在此示例中，我将创建一个事件，当将文件放入特定文件夹时将触发该事件。

Update the following details for the alert:

更新警报的以下详细信息：

• Name

  名称

• Type
  In this case, we will specify the WMI event alert

  类型
  在这种情况下，我们将指定WMI事件警报

• Namespace
  This must be a namespace on the same computer on which SQL Server Agent is running. In this case, it will be root\cimv2

  命名空间
  它必须是运行SQL Server代理的同一台计算机上的名称空间。 在这种情况下，它将是root \ cimv2

• 
  This is the WQL query for which the alert should be triggered. If a file appears in the C:\WMITest folder the event will be triggered. The WITHIN 5 indicates the polling interval, which is set to 5 seconds here.
  这是应该为其触发警报的WQL查询。 如果文件出现在C：\ WMITest文件夹中，则事件将被触发。 WITHIN 5表示轮询间隔，此处将其设置为5秒。

WQL queries can be a bit daunting if you are not used to using them. There is a nifty tool which ships with Windows which can help you to test your WQL queries. You can access it by going to Run and typing wbemtest.

如果您不习惯使用WQL查询，可能会有些困难。 Windows附带一个漂亮的工具，可以帮助您测试WQL查询。 您可以通过运行并输入wbemtest来访问它。

This tool will help you to test your WQL queries. If you are interested in more detailed information on this tool, please read this excellent article WMI Query Language by Example

该工具将帮助您测试WQL查询。 如果您对该工具的更多详细信息感兴趣，请阅读这篇优秀的文章WMI Query Language by Example

结论 (Conclusion)

The SQL Server Agent alert framework allows for a lot of flexibility in terms of notification and reactive responses which can be extremely useful in cases where a known recurring issue requires a known action in response.

SQL Server代理警报框架在通知和响应式响应方面提供了很大的灵活性，这在已知的重复出现问题需要已知的响应措施的情况下非常有用。

翻译自: https://www.sqlshack.com/create-configure-sql-server-agent-alerts/