Wednesday, 20 February 2013

Create a SQL Server event alert


In this Example we will configure SQL Server Agent to transmit an alert if an error of severity level 17 occurs and alert the operator Operator_admin when it happens

·         In Object Explorer, expand SQL Server Agent, right-click Alerts, and then click New Alert.
·         In the New Alert dialog box, add a new alert with the properties in the following table.

·         Configure the alert as below :

Name: Sql Server Resource alert
Type: Sql server event alert
Database Name: MyuserDB
Severity: 017-Insufficient Resources
Raise alert when Message Contains: Cleared

·         In The Response Panel select the Notify operators check box, and then select the E-mail check box for the Operator_Admin operator
·         On the Options page, select the E-mail check box, in the Additional notification message to send box, type Warning: An error occurred in MyuserDB database, and then click OK.

To test that the alert is triggered when the condition is met
·         Connect to The instance using SSMS

·         On the File menu, point to New, and then click Query with Current Connection.

·         In the query pane, type the following Transact-SQL code.

USE MyUserDB
GO
RAISERROR (‘Test Error’, 17, 1) WITH LOG

·         On the Query menu, click Execute.

·         Switch to Outlook and verify the new mail exists.

No comments:

Post a Comment