Using System Monitor, you can create an alert to be raised when a threshold value for a System Monitor counter has been reached. In response to the alert, System Monitor can launch an application, such as a custom application written to handle the alert condition. For example, you can create an alert that is raised when the number of deadlocks exceeds a specific value.
Alerts also can be defined using Microsoft SQL Server Management Studio and SQL Server Agent. For more information, see Alerts.
To set up a SQL Server database alert
On the navigation tree of the Performance window, expand Performance Logs and Alerts.
Right-click Alerts, and then click New Alert Settings.
In the New Alert Settings dialog box, type a name for the new alert, and then click OK.
On the General tab of the dialog box for the new alert, add a Comment, and click Add to add a counter to the alert.
All alerts must have at least one counter.
In the Add Counters dialog box, select a SQL Server object from the Performance Object list, and then select a counter from the Select counters from list.
To add the counter to the alert, click Add. You can continue to add counters, or you can click Close to return to the dialog box for the new alert.
In the new alert dialog box, click either Over or Underin the Alert when the value is list, and then enter a threshold value in Limit.
The alert is generated when the value for the counter is more than or less than the threshold value (depending on whether you clicked Over or Under).
In the Sample data every boxes, set the sampling frequency.
On the Action tab, set actions to occur every time the alert is triggered.
On the Schedule tab, set the start and stop schedule for the alert scan.