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.
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.