SQL Server Agent Performance Alerts
Why?
On some
scenarios we need our SQL server to take some proactive actions rather than
notifying us about the error & issues. Imagine we are getting a bulk insert
failing simply for as there is no room for the LDF to grow further or we need
to disable some user access when a suspected login attempt to connect SQL or
there may be huge similar scenarios . SQL server 2012 has the ability to help
us with its capabilities. So let’s see how J
How?
In SQL
Server 2012 we have an enhanced SQL Server Performance Alerts system
An alert is
a predefined response to an event.
Alerts can
be triggered by
·
Logged
SQL Server events.
·
WMI
Events
·
SQL
Server Performance conditions
Alerts can
·
Notify
an operator
·
Start
a job
Scenario:
Here I have a SQL database “Servercounts” to which we are
going to design an alert.
If its Log file grow more than 40% we need SQL server to
send a notification to corresponding team using a DB email operator & it
should trigger a log backup job so that the LDF will get some free space within
it to grow further.
Let’s Begin:
Log Into your SQL 2012 instance.
·
On SQL server Agent open Alerts –> New Alerts
·
I had named this Alert as T_log_growth
·
There are 3 types available for our selection
>> SQL Server Event Alert
>>SQL
Server Performance Condition Alert
>>
WMI Event Alert
Each one has
its own speciality functions.
We will concentrate on “SQL
Server Performance Condition Alert” for our scenario. But will have a
glimpse of other things too.
Type: SQL Server Event Alert:
·
This is specifically designed to send alert if
any event had occurred. Such as any severity error had occurred.
·
You may select for all databases or to any
specified database if you want to investigate.
·
You may also specify SQL to send alert if it
come across any particular error message on the error log like “wait time
increases, login failed for users, etc.”
Type : WMI Event Alert
·
In this type we can monitor all WMI events
related to SQL database.
·
For example, Create Database, Alter Database,
Drop database can be monitored & any occurrences of above mentioned events
will trigger an alert to selected operator.
Type : SQL Server Performance Condition Alert
Agenda:
·
Now I name my Alert as T_Log_Growth
·
I am going to monitor Log growth of Servercounts
Database
·
When the Log grows over 40 % it should trigger
an alert & notify us.
·
Also it should start a job to initiate a log
backup on the database.
On Response tab :
·
Select the Backup job to be executed.
·
Select the operator through which you need to be
notified from SQL.
If you want to get the error message text
with your alerts you may set them in Options tab.
From History tab you may get details like
number of occurrences & last time when the alert got triggered.
So we are done with configurations now, I
will take help of performance monitor to show you the growth of log & the
decrease of the same once the log backup completed.
I am going to insert 50000 rows on a test
table on servercounts database.
Notice A à
the insert started.
Bà the LDF goes beyond
40% & our Alert will triggered & email will be sent.
Cà Insert is going on
& utilization of Log
Dà Once the transaction
is over a TLOG backup will be triggered by the alert & once the transactional log backup is done the log file
usage falls down to normal.
The alert message sent will be like
Checking History Tab :
Thus using enhanced SQL Server Agent Performance Alerts we
triggered alerts & also provided immediate solution for increasing T-LOG
issues.
========================================================================
Thank you for coming till this point see you again with some
interesting stuffs.
VSG,
SQLDBA
