To a proactive database administrator, SQL Server Agent is one of the main tools to monitor different activities continuously happening in SQL Server instances.
SQL Agent provides us with the ability to automate such monitoring tasks and respond with the proper action, or, at a minimum, notify the related person, when a specific error, such as performance issue or a Windows event is detected. Automated monitoring and response to the different SQL Server, performance or Window events is performed using the SQL Agent Job Alerts.
SQL Agent Job Alerts Overview
SQL Agent benefits from the different Windows and SQL Server events that are written to the Windows Application logs in the server by reading these events to find a match between these events and the defined alerts. When a match is found, a defined alert will be fired, and the response action will be executed.
To create an alert, you need to provide a meaningful name of the alert, less than 128 characters, the type of the event that will fire this alert and finally the action that will be performed by the SQL Server Agent as a response to that event. In addition, you should be member of SYSADMIN fixed server role to be able to run the sp_add_alert system stored procedure and create the alert.
There are three types of alerts that can be created in the SQL Server Agents:
- SQL Server Event Alerts: This alert will be fired when an event with a specific error number or an error severity level occurs
- SQL Server Performance Event Alerts: This alert will be fired when a specific performance counter exceeds, equal to or falls below a predefined threshold value
- WMI event alerts: This alert will be fired when a specific Windows Management Instrumentation (WMI) event occurs
In this article, we will concentrate on creating and configuring alerts for SQL Server Agent jobs using ApexSQL Job.
ApexSQL Job can be downloaded from the ApexSQL download center and installed to your machine easily, by following a straight-forward installation wizard, in which you will be asked to provide the installation path only.
Getting started
After installing ApexSQL Job to your machine, click on its “job organization bag” icon to start using it in configuring the SQL Agent alerts. To add a new SQL Server instance, click on the Add button, from the SQL Server tasks category, under the Home tab, as shown below:
In the displayed Add SQL Server windows, provide the name of the SQL Server instance that you will connect to, and the authentication method and credentials that will be used to connect to the provided SQL Server instance, as follows:
As ApexSQL Job can be used to organize SQL Server Agent jobs, schedules and alerts on multiple instances at the same time, it organizes the different SQL Server instances within folders. So that, when a new SQL Server instance is added, you will be asked to add that instance under an existing folder, or create a new folder, as shown below:
After adding the SQL Server instance, you need to make sure that the SQL Agent service is up and running. To do that, click on the Start button, from the Agent tasks category, under the Home tab. If the service is already running, it will perform no action except for notifying that it is already running, as below:
And if the SQL Server Agent service is stopped, it will start the service and notify you with that action as follows:
In ApexSQL Job, there is a separate tab for creating, editing and deleting the different types of SQL Agent Job Alerts, as shown below:
ApexSQL Job allows you to create the three SQL Agent Alerts types described previously. To make it clear, we will discuss one example per each alert type using the ApexSQL Job:
SQL Server Event Alert
SQL Server Agent Event Alert type is useful when you plan to perform a specific action in response of receiving a specific SQL error number or error severity level.
To create a new alert, click on the Add button, under the Alerts tab, and the Create alert window, will be displayed. The Create alert window will ask you to provide an indicative name for the alert, enable or disable that alert, and the type of the alert to be created. In this example, we will choose SQL Server Event Alert type, as below:
Under the SQL Server Agent Event Alert type, you will have the option to specify the database on which this alert will be applied and if the alert will be fired in response to a specific SQL Server error number or error severity level.
For example, if you plan to monitor the failed logins in the SQL Server instance, configure the alert to be raised based on the error number 18456 occurrence. For more information about the different error numbers that can be received in SQL Server Engine, check the SQL Server error codes list.
ApexSQL Job allows you also to configure the alert to be raised based on different error severity levels, as shown below:
For example, you can monitor the failed logins by configuring the SQL Server Agent alert to be raised based on error severity 014 – Insufficient Permission, as below:
You have the option also to configure the alert to be fired if an error that contains a particular text in the event message is occurred, in case you recall part of the message text.
After configuring on which basis the alert will be fired, you will be asked to specify what action should be performed as a response for the occurred event. On the Create Alert window, click on the Response page to configure the response type, as follows:
From the Response page, you can see that there are two types of responses for the event occurrence:
- Execute job: Select which SQL Server Agent job will be executed in response to the event occurrence
- Notify operators: Provide a predefined operator or create a new operator to notify when the alert event occurred. Operators enable notification and monitoring capabilities of SQL Server Agent, by monitoring the different aspects of the SQL Server Engine and notify the administrators using email notification, pager email notification or net send notification. Check Operators for more information
In our example, we will execute a SQL Agent Job that logs all failed logins when an error with severity 14 is received.
In the Options page of the Create Alert window, you can optionally specify what will be included in the notification message, and how much time the two consequent responses will be delayed, as shown below:
Once created, the SQL Server Agent alert can be configured, monitored or deleted from the Alerts tab:
The same alert can be also created, checked and configured from the Jobs tab of the SQL Agent Job properties, where you can find the previously created alert on the related SQL Agent job, as follows:
If we simulate a number of failed login attempts, then check the target job history, from the History tab. ApexSQL Job you will see that the job is executed after being invoked by Alert 2, as shown below:
In addition, you can check how many times this alert occurred, when the last time this alert was fired and when the last response for the firing event occurred, from the History tab of the created SQL Server Agent alert, that will appear after creating the alert, with the ability to reset the occurrences count, as follows
SQL Server Performance Condition Alert
This type of alerts is raised in response to a specific performance condition. Where you specify the performance counter that you plan to monitor and a monitoring threshold for that counter.
To create a SQL Server performance condition alert, click on the Add button under the Alerts tab. In the Create Alert window, specify an indicative name for the alert, specify to enable or disable that SQL Server agent alert, and specify the type of the alert to be SQL Server performance condition alert, as below:
In the Performance condition alert definition section, provide the Object as the area of performance to be monitored, from the available options below:
After specifying the performance area, select the performance counter from the counters list, that you plan to monitor. In addition, specify the threshold, a number describes that selected counter for the alert, and the behavior of the counter that fires the alert. For example, the alert can be fired if the current counter value rises above, becomes equal to or falls below the defined counter threshold.
In our example, we will monitor if the number of created transactions in the connected SQL Server instance exceeds 100 transactions. And as a response, we will execute a SQL Server Agent job that takes a transaction log backup for the users’ database to prevent running the transaction log file out of free space, as shown below:
After performing heavy load of transactions on the monitored SQL Server instance, you will see that the transaction log backup job is executed successfully after being invoked by the Alert 5, as shown below:
In addition, you can check how many time this alert occurred, when the last time this alert fired and when the last response for the firing event occurred, from the History tab of the created alert, with the ability to reset the occurrences count, as below:
WMI Event Alert
The functionality of SQL Server Agent alerts extends the scope of monitoring the SQL Server events, by allowing you to fire an alert in response to a particular Windows event.
To create WMI event alert, click on the Add button under the Alerts menu. In the Create alert window, specify an indicative name for the alert, specify to enable or disable that alert, and specify the type of the alert to be WMI event alert, as below:
In the WMI event alert definition section, specify the Namespace, where the SQL Server Agent will register itself as a WMI client, on the same computer where it is running, to the WMI Namespace provided to query for the event. In addition, specify the Windows Management Instrumentation Query Language statement, also known as WQL, that will be used to identify a specific event.
In our example, we will create an alert that is raised based on the DDL changes, such as ALTER DATABASE statement, on the connected instance. If any DDL change is performed, the alert will be fired and respond by executing a SQL Server Agent Job that audits these changes to an auditing repository, as shown below:
After executing number of ALTER DATABASE statements on the monitored SQL Server instance, you will see that the Log_DB_Changes job will be executed successfully after being invoked by the Alert 8, as shown below:
Again, the number of times this alert occurred, the last time this alert fired and the last response for the raised event occurrence, can be checked from the History tab of the created alert, with the ability to reset the occurrences count, as below:
February 28, 2019
