Data Management

SQL Server Agent Alerts

In SQL server, an alert can be defined as a predefined response to an event or a performance trigger. An alerts can notify an operator by sending an email or start a job.

To be able to set up an alert, you need to enable SQL Server Agent and to do so, you can use SQL Server Configuration Manager.

Using SQL Server Configuration Manager to check if SQL Server Agent is up and running.
Using SQL Server Configuration Manager to check if SQL Server Agent is up and running.

Depending on the type of the event, we need to choose one of the three types of alerts :

  • SQL Server Event Alert
  • SQL Server Performance Condition Alert
  • WMI Event Alert

In this post we’ll take a look at SQL Server Performance Alert.

We will set up an alert against a test database called Alert_DB, we’ll be monitoring the Percent Log Used in order to raise the event when the counter rises above 60%. The response will be a job that will backup the log file and reclaim the space in the log file.

Step 1: Creating the database

Let’s start by creating a test database with full recovery model selected and then creating a test table.

Step 2: Creating the Job

To create a job that will backup the Log file, run the following T-SQL.

Step 3: Defining the alert

Step 4: Testing

To test what we have done in the previous steps, let’s run the following code which will try to fill the log File.
In the same time we will be monitoring our database percent log file used, using the performance monitor .

As you can see in the following gif, when the size of the log file increased above 60%, the alert called the job, which backup the log file and reclaimed the log file space.

Performance monitor

1 reply on “SQL Server Agent Alerts”

Thank you for these tips. I didn’t use SQL Server Agent Alerts before reading this article.

Leave a Reply