SQL Server Agent Alerts

Source: Internet
Author: User

Prerequisites for using SQL Server Agent alerts
1. Create the operator, the user who receives the message
2. Create an alert, meet a certain condition to trigger an alert, and respond (execute job or/and notify operator)
3. Configure Database Mail for sending message notifications
4, SQL Server Agent, Properties---Alert system, mail configuration file, select the corresponding mail system and configuration file
For detailed configuration steps, refer to SQL Server Agent alerts and operator and SQL Server Agent configuration Database Mail
After the test server is configured as described above, the simulation satisfies the conditions that triggered the alert and does not appear as expected, and the SQL Server Agent logs are as follows:

[264] An email session has not been established to attempt to send an e-mail. Search for problems simply restart the agent service.
First, how to send alerts to multiple people?
1, operator--general--e-mail name, according to MAIL1;MAIL2;MAIL3 this format, will need to send the email address plus:

2. Create a new operator, and then tick the alert's response page to select the operator to notify:

is the e-mail message name with multiple addresses and notifications for multiple operators to send records:

Ii. How is the date of the last alert and the date of the last response defined?

Meet the conditions to trigger an alert, update the date of the last alert, or, if there are set execution jobs or/and notify operators in the response, update the date of the last response when performing these actions.
msdb: Sp_sqlagent_get_perf_counters is used to fire the SQLAgent alerts.
whenever there is alert set,this procedure are executed in background to determine the threshold limits, being it an alert For any performance condition.
You mention the It runs frequently,but by default it seconds. Theres a registry key to change the interval hklm\software\microsoft\microsoft SQL server\mssql10_50.sql08r2\ Sqlserveragent\performancesamplinginterval

By default, every 20 seconds checks whether an alert is triggered, only if the condition is met, and then executes the job or/and notifies the operator based on the response. The response is limited to the [two response delay between responses] in the option, and the delay time is too frequent to avoid sending too many notifications to explode the mailbox.
The test creates the following alerts:

There are only SQL Server specific performance counters, and there are no system counters such as CPUs:

The Database Mail log for the alert corresponds to the following:

Since the condition that triggered the alert has been met in the test, it can be seen from the Database Mail log that a message is sent every 20 seconds.
The alert messages received are as follows:

Note that the data received by the response operation is the data at the moment of response. For example, setting the delay time between [two responses] to 10 minutes in the test above, actually triggering an alert every 20 seconds, but sending the message once every 10 minutes. If we modify log File (s) used Size (KB) in the first 8 minutes, the message sent in the 10th minute will be the modified numeric information.

SQL Server Agent Alerts

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.