An alert responds to a specific event. The alarm responds to the following event types:
1. SQL Server event:
You can specify an alert to respond to one or more events. Use the following parameters to specify the event that triggers the alarm:
Error Code
The SQL Server Agent sends an alert when a specific error occurs. For example, you can specify error 2571 to respond to unauthorized attempts to call Database Console Commands (DBCC.
Severity Level
The SQL Server Agent sends an alert when a specific level of serious error occurs. For example, you can specify severity level 15 to respond to syntax errors in a Transact-SQL statement.
Database
The SQL Server Agent sends an alert only when an event occurs in a specific database. This option is a supplement to the error number or severity level. For example, if an instance contains a database for production and a database for reporting, you can define an alert that only responds to syntax errors in the production database.
Event text
The SQL Server Agent sends an alert when the event message of a specified event contains a specific text string. For example, you can define an alert to respond to messages containing specific table names or constraints.
2. SQL Server performance conditions
You can specify an alarm to respond to specific performance conditions. In this case, you need to specify the performance counter to be monitored, the threshold of the alarm, and the operations that the counter must perform when the alarm occurs. To set performance conditions, you must define the following items on the "General" page in the "new alarm" or "alarm properties" dialog box of the SQL Server Proxy:
Object
The object is the performance area to be monitored.
Counter
A counter is a property of the region to be monitored.
Instance
An SQL Server instance defines a specific instance (if any) of the attributes to be monitored ).
"Trigger alert when the counter meets the following conditions" and "value"
The threshold value of the alarm and the behavior that causes the alarm. The threshold value is a number. The action is one of the following: "lower", "equal", or "greater. "Value" is the number that describes the performance condition counter. For example, to set an alert for the performance object SQLServer: Locks when the Lock Wait Time exceeds 30 seconds, you can select "greater than" and specify 30 as "value ".
For example, an alarm can be triggered when the available space in tempdb is less than 1000 KB for the performance object SQLServer: Transactions. To do this, select the counter Free space in tempdb (KB), "less than", and "value" 1000.
3. WMI event
You can specify an alert to respond to a specific WMI event. To select a WMI event, you must define the following content on the "General" page in the "new alarm" or "alarm properties" dialog box of the SQL Server Agent:
Namespace
The SQL Server Agent is registered as a WMI client in the WMI namespace (using this namespace to query events.
Query
The SQL Server Agent identifies a specific event using the provided Windows Management Instrumentation Query Language (WQL) statement.
The following links point to common tasks:
Create Alarm Based on Message number
SQL Server Management Studio
Transact-SQL
SQL Server Management object (SMO)
Create alert based on severity level
SQL Server Management Studio
Transact-SQL
SQL Server Management object (SMO)
Create alert based on WMI events
SQL Server Management Studio
Transact-SQL
SQL Server Management object (SMO)
Define the response to the alert
SQL Server Management Studio
Transact-SQL
SQL Server Management object (SMO)
Create error messages for user-defined events
Transact-SQL
Modify error messages for user-defined events
Transact-SQL
Delete error messages for user-defined events
Transact-SQL
Disable or reactivate an alarm
SQL Server Management Studio
Transact-SQL
SQL Server Management object (SMO)