Alert feature in Microsoft SQL server is one of the prominent feature available in SQL server. Before we understand what Alert feature is and how to make use of the feature, first let us understand about basic meaning of Alert.
Alert is a warning or notification highlighted or notified to individual, group or other subsequent action for the predefined scenarios. Having alert system will keep away any of the future hazardous or hefty loss.
In SQL server Alert work similar as the Alert system available in any of the other systems available in other systems or process. To feel the alert system how it works you can consider the smartphones or any other mobile devices which through warning when there Is a low battery or any communication received.
As simple example earlier, SQL server alert system behaves the same way where it notifies the end user about any performance issues, Starting or stopping the process, intimating the end user through mail or other any communication methods.
SQL server Alert system is managed through SQL Server agent and SQL server agent should be up and running to make use of SQL server Alert System feature.
SQL Server Alert system can notify the end users through following communication methods.
- Net share
- In SQL server 2014 there are three type Alert system
- SQL Server Event Alert
- SQL server Performance Condition Alert
- WMI Event alert
- The following steps will instruct to set up Alert system in SQL server
- Expand SQL server agent and make sure SQL server agent is up and running.
- Right click on the alert system and click new Alert
- Post to the previous step alert configuration window will be open. In the configuration window update the properties for the alert. In our example we have considered Alert system for SQL server performance condition alert type and the properties provided for Alert system will change according to the type of alert selected.
- Name: Provide the alert Name which is going to be the identification for the Alert.
- Type: Select the type of Alert (In this case we have selected SQL server performance condition alert.)
- Counter: Configure the counter to choose the required counter ( In our Case it SQL server databases)
- Instance : Configure the Instance for which Notification has to be processed ( Note: This is a counter instance and not server instance)
- Alert: Here we will configure when the alert should be configured. In this case alert will be triggered if ‘Dageop’ log file exceed 75%
- Move to the response tab provided in the left pane of configuration window. In the response window configure the Alert either to send the mail or to trigger some process or it might be both. In the below case we have considered both to send mail and also to trigger the ‘Flush_Dageop_Log’
- The above steps will create Alert called ‘Log_Exceed_Alert’ which can be later redefined by double clicking the Alert from Alert folder in SQL server agent. This Alert will trigger Flush_Dageop_Log package to reduce the growth of Dageop database log file and also notification will be provided to the end user.