Message send notification when a configuration job fails in SQL Server R2

SQL Server routine maintenance will inevitably encounter job failures. After the failure naturally need to know that it failed, in addition to routine inspection can be found in the error, there is a more real-time monitoring is still necessary. More professional monitoring system such as scom although can monitor job execution in error when the alarm, but for the DBA may be not high, the most important thing is the person responsible for monitoring after seeing the alarm generally need to contact the DBA immediately, for some of the less important jobs fail, It must be unpleasant to call you up in the middle of the night. SQL Server itself supports sending Database Mail, combined with the ability to send messages, to notify DBAs of errors after a job fails, which is a great help in understanding whether job execution is abnormal.

The configuration steps are as follows

1. Configure Database Mail

Under the Management node, locate Database Mail, right-click, and select Configure Database Mail

The Select Configuration task step of the Configuration Wizard allows you to select the first item so that you can configure the items by following the wizard steps.

After clicking "Next", if the Database Mail feature was not previously enabled, a dialog box will appear asking if it is enabled. To continue, it is natural to choose "Yes".

The Enable Database Mail feature is actually enabled for databases mail XPS server settings

can be done in advance via SQL statements, as follows:

[SQL]View PlainCopy

1. exec sp_configure ' show advanced Options ', 1
2. GO
3. RECONFIGURE
4. GO
5. exec sp_configure ' Database Mail XPs ', 1
6. GO
7. RECONFIGURE
8. GO

To create a new profile step, you need to specify the profile name and set it as needed. The description is not required.

At the same time, you must add an SMTP account to log on to the mail server to send mail. Click the "Add" button.

In the New Database Mail Account dialog box, you need to specify the account name, e-mail address, server name, and how to select Authentication. Generally we connect to the mail server is required by the user name and password authentication, so select Basic authentication, and fill in the correct user name, password and Confirm password.

This completes the STMP account add, click "Next" to continue

The Manage profile security step can either maintain the default settings or be set according to the actual security requirements.

In the "Configure system parameters" step, set as appropriate as necessary. The number of account retries is necessary to set up, so that in the event of a short period of time the mail server can not connect to try to avoid the notification message is not received. The next step is to complete the wizard.

After you have configured your Database Mail, it's a good idea to test your database Mail work properly. Right-click the Database Mail item and select Send Test e-mail.

The recipient fills in the e-mail address to receive the message. When you click the Send Test email button, the data tries to send the message. Just a moment. Let's check to see if the email account received the email. If you do not receive it, you should check the email account information before configuring it correctly.

Metabase mail is configured with SQL statements as follows

[Delphi]View PlainCopy

2. --Create a configuration file
4. EXEC msdb.dbo. SYSMAIL_ADD_PROFILE_SP
6. @profile_name = ' Mailconfigforjob ',
8. @description = ' database job notification use ';
12. --Add SMTP mail account
14. EXEC msdb.dbo. SYSMAIL_ADD_ACCOUNT_SP
16. @account_name = ' Mailaccountforjob ',
18. @description = ' job notification email account ',
20. @email_address = ' [email protected] ',
22. @mailserver_name =' smtp.test.com ',
24. @port = .
26. @username = ' Jobalert ',
28. @password = ' UserPassword ';
32. --Associate the email account with the configuration file
34. EXEC msdb.dbo. SYSMAIL_ADD_PROFILEACCOUNT_SP
36. @profile_name = ' Mailconfigforjob ',
38. @account_name =' Mailaccountforjob ',
40. @sequence_number = 1;

2. Adding an operator

The purpose of adding an operator is to specify the recipient who receives the email notification.

Under SQL Server agent, right-click operator, select New operator

In the new operator window, you only need to specify name and email address names. The e-mail name refers to the email address where the message was received.

The SQL statement that created the operator is

[SQL]View PlainCopy

1. EXEC msdb.dbo.sp_add_operator
2. @name = N' jobmonitor ',
3. @email_address =n' jobmonitor ';

3. Set up notifications for jobs

Suppose you want to set a mail alert on an existing job, select the "Notifications" tab in the Job properties, click on the "email" checkbox on the right, and then select the operator "Jobmonitor" that you created earlier in the first drop-down list, and then "when the job fails" as the default selection, you can also change to "When the job is successful" or "when the job is completed," This is based on actual needs. For the job more than the situation set job success also receive mail, to often view a lot of notification messages, it is easy to cause visual fatigue, it is possible to ignore the failure of the message. Of course, sometimes the server exception causes the job is not executed, will not send any mail, if the completion of the message can be determined that the job has been executed, and suddenly one day did not receive the mail, it has to take the initiative to check.

The SQL statement that sets the notification is

[SQL]View PlainCopy

1. EXEC msdb.dbo.sp_update_job
2. @job_name = ' Test ',
3. @notify_level_email = 2,
4. @notify_email_operator_name = N' jobmonitor ';

Message send notification when a configuration job fails in SQL Server R2