本文共 11514 字,大约阅读时间需要 38 分钟。
Before creating a SQL Server alert, make sure that Database Mail and SQL Server Agent properties are properly configured
在创建SQL Server警报之前,请确保正确配置了数据库邮件和SQL Server代理属性。
Right-click Database Mail and select Send Test Database mail
If you receive the message that there are no Database mail profiles, you have to create an email profile first右键单击“ 数据库邮件”,然后选择“ 发送测试数据库邮件”
如果收到消息说没有数据库邮件配置文件,则必须先创建一个电子邮件配置文件Create a database mail account using your mail server parameters and credentials
使用您的邮件服务器参数和凭据创建数据库邮件帐户
Create a database mail profile and add the mail account created in the previous step
创建数据库邮件配置文件并添加在上一步中创建的邮件帐户
Another very important step, often skipped when creating an alert, is setting SQL Server Agent properties:
创建警报时通常会跳过的另一个非常重要的步骤是设置SQL Server代理属性:
Make sure that Include body of e-mail in notification message is selected
确保已选中在通知消息中包括电子邮件正文
If the mail profile is not enabled for SQL Server Agent, you will be able to create SQL Server alerts and set email notifications successfully. There will be no error messages, nor warnings, yet emails will not be sent. To find out why the email notifications are not sent, you will have to check the SQL Server error logs, they will clearly indicate that the problem is that Database Mail is not enabled for SQL Server Agent notifications
如果未为SQL Server代理启用邮件配置文件,则可以创建SQL Server警报并成功设置电子邮件通知。 没有错误消息,也没有警告,但不会发送电子邮件。 要找出为什么不发送电子邮件通知的原因,您将必须检查SQL Server错误日志,它们将清楚表明问题是未为SQL Server代理通知启用数据库邮件
Alerts can be created using SQL Server Management Studio or T-SQL. When creating an alert, you have to specify its name, the event or threshold value that fires the alert, and the action executed when the alert is fired
可以使用SQL Server Management Studio或T-SQL创建警报。 创建警报时,必须指定其名称,触发警报的事件或阈值以及触发警报时执行的操作
These object types can be obtained by executing
这些对象类型可以通过执行
select distinct object_name from sys.dm_os_performance_counters
As the normal values for Page Life Expectancy are above 300 seconds (5 minutes), we’ll select falls under and enter 300 in the Value field
Note that the counters are queried periodically and that there might be a slight delay before the alert is triggered
Here is an example for setting Memory Grants Pending. Its value represents the total number of SQL Server processes waiting to be granted workspace in the memory. The threshold value is zero. If any processes are waiting for the memory workspace, they are queued and the Memory Grants Pending value is higher. The alert will be fired if the counter rises above 0
由于“ 网页预期寿命”的正常值超过300秒(5分钟),因此我们将选择“ 低于” ,然后在“ 值”字段中输入300
请注意,会定期查询计数器,并且触发警报之前可能会稍有延迟
这是设置Memory Grants Pending的示例。 它的值表示等待在内存中被授予工作区SQL Server进程总数。 阈值为零。 如果有任何进程在等待内存工作空间,则它们会排队,并且“ 内存授权未决”值会更高。 如果计数器上升到0以上,则会触发警报
To send a notification, select Notify operators
要发送通知,请选择“ 通知操作员”
If the operator list is empty, select New Operator and fill in the records
如果操作员列表为空,请选择“ 新操作员”并填写记录
In the Notifications tab, select E-mail for the alert type
在“ 通知”选项卡中,选择“电子邮件”作为警报类型
Another way to add a notification is using the sp_add_notification stored procedure
添加通知的另一种方法是使用sp_add_notification存储过程
USE [msdb]GO EXEC dbo.sp_add_notification @alert_name = N'PLE_Alert', @operator_name = N'Millie', @notification_method = 1 GO
The @notification_method parameter can be 1,2, or 4 for emails, pager, and net send, respectively. Only members of the sysadmin SQL Server role can execute the sp_add_notification stored procedure
对于电子邮件,寻呼机和网络发送,@ notification_method参数可以分别为1,2或4。 只有sysadmin SQL Server角色成员才能执行sp_add_notification存储过程
Select to include the error message into emails sent and add additional text that will provide more details about the situation that triggered the alert
选择以将错误消息包含在已发送的电子邮件中,并添加其他文本,以提供有关触发警报情况的更多详细信息
Open the History tab, to see how many times the SQL Server alert has occurred, the last occurrence, and the last response to the alert. To reset the number of captured alerts and other parameters shown in this tab, check the Reset count check box
打开“ 历史记录”选项卡,以查看SQL Server警报发生了多少次,最后一次发生以及对警报的最后响应。 要重置此选项卡中显示的已捕获警报的数量和其他参数,请选中“ 重置计数”复选框
The same alert can be created using T-SQL and the dbo.sp_add_alert stored procedure. Note that it has to be executed against the msdb database by a member of a sysadmin role
可以使用T-SQL和dbo.sp_add_alert存储过程来创建相同的警报。 请注意,它必须由sysadmin角色的成员针对msdb数据库执行
USE [msdb]GO EXEC msdb.dbo.sp_add_alert @name=N'PLE_Alert', @message_id=0, @severity=0, @enabled=1, @delay_between_responses=300, @include_event_description_in=1, @notification_message=N'Page Life Expectancy has fallen under 300 seconds', @category_name=N'[Uncategorized]', @performance_condition=N'Buffer Manager|Page life expectancy||<|300', @job_id=N'00000000-0000-0000-0000-000000000000'GO
When an alert is fired on the FUJITSU\SQL2012 instance, you’ll get an email with the SQL Server Alert System: ‘PLE_Alert’ occurred on \\FUJITSU\SQL2012 subject and the following text in the email body:
在FUJITSU \ SQL2012实例上触发警报时,您将收到一封包含SQL Server警报系统的电子邮件:\\ FUJITSU \ SQL2012主题上出现了“ PLE_Alert”,并且电子邮件正文中包含以下文本:
DATE/TIME: 2/19/2014 1:08:41 PM
日期/时间:2014/2/19下午1:18:41
DESCRIPTION: The SQL Server performance counter ‘s’ (instance ‘s’) of object ‘s’ is now below the threshold of s (the current value is s).
描述:对象“ s”SQL Server性能计数器“ s”(实例“ s”)现在低于s的阈值(当前值为s)。
COMMENT: Page Life Expectancy has fallen under 300 seconds
评论:预期页面寿命低于300秒
JOB RUN: (None)
求职:(无)
Disabling an alert
To stop the alert from being fired, but still keep it as inactive, disable it. You can easily enable it and reuse later禁用警报
要停止触发警报,但仍将其保持为非活动状态,请将其禁用。 您可以轻松启用它,以后再使用The same can be done using T-SQL. Only members of the sysadmin SQL server role can execute the sp_update_alert stored procedure
使用T-SQL可以完成相同的操作。 只有sysadmin SQL Server角色成员才能执行sp_update_alert存储过程
USE msdbGO EXEC dbo.sp_update_alert @name = N'Test Alert', @enabled = 0GO
When set to 0, the @enabled parameter disables the alarm. To enable the alarm, use same code except for @enabled = 1
设置为0时,@enabled参数禁用警报。 要启用警报,请使用相同的代码,但@enabled = 1除外
Note that all T-SQL commands given in this article, unless qualified as msdn. dbo.<procedure_name> have to be executed against the msdb database. Otherwise, you’ll get the error message that the procedure cannot be found
请注意,除非具有msdn的资格,否则本文中提供的所有T-SQL命令。 dbo。<procedure_name>必须针对msdb数据库执行。 否则,您将收到错误消息,指出找不到该过程
As shown, the Alert feature in SQL Server Agent is very useful for automating performance issue notifications. It enables creating SQL Server alerts for every available SQL Server performance counter. For each counter, you can set a custom threshold value and specify whether the expected values should be higher or lower. However, it is not possible to set two values for a counter in the same alert. For example, if the counter value is lower than the value A – fire an alarm with a notification about a medium severity issue, and if the counter value falls below the value B – fire a critical alarm
如图所示, SQL Server代理中的警报功能对于自动执行性能问题通知非常有用。 它允许为每个可用SQL Server性能计数器创建SQL Server警报。 对于每个计数器,您可以设置一个自定义阈值并指定期望值应该更高还是更低。 但是,不可能在同一警报中为计数器设置两个值。 例如,如果计数器值小于值A –发出带有中等严重性问题通知的警报,并且如果计数器值小于值B则触发严重警报
Email notifications can be seamlessly sent to a number of operators, or to a mailing list. Also, sending pager and net send notifications are available, but will be depreciated in the future SQL Server versions
电子邮件通知可以无缝地发送给许多操作员或邮件列表。 此外,还可以使用发送寻呼机和网络发送通知,但在将来SQL Server版本中将不推荐使用
Setting multiple alarms is not very user friendly, as you have to create a single alarm for each counter you want to monitor, therefore go through a number of steps. The feature is supported only in Enterprise and Business Intelligence SQL Server editions
设置多个警报不是非常用户友好,因为您必须为要监视的每个计数器创建一个警报,因此需要执行许多步骤。 仅企业版和商业智能SQL Server版本支持该功能
翻译自:
转载地址:http://qknwd.baihongyu.com/