Alerting is a critical and essential feature for
any IT system especially database management systems, and that is why “SQL
Alerts” was an essential part of SQL Server Agent since earlier versions of SQL
Server.
SQL Server simply enables DBAs to create alerts
with specific response based on specific event that related to specific
category, these categories are the following:
1.
SQL Server Event, the events in this category are based on the SQL
Server error messages number or severity.
2.
SQL Server Performance Condition, the events in this category are
based on SQL Server Performance Counters “part of Windows Performance
Counters”.
3.
WMI Event, the events in this category are simply based on WMI
queries.
Then, the question here is “what if I need to
get alerted with any other events that outside these categories?”
For example what if I need to get alerted with
locking event after a specific duration “3 minutes for example” or get alerted
when the used amount of memory buffer pool exceeded a specific threshold?
The simple answer is that we need to create our
own customized alerts, this can be done by using two of the built in features
inside SQL Server database engine: Database Mail and SQL Agent Jobs.
Then it is very simple, the scenario should be
as the following:
1.
Defining the event we need to get alerted with.
2.
Defining the threshold for the alert of this event.
3.
Defining the frequency for listing for this event.
4.
Collecting the required information for this event to be sent in
case of alerts.
5.
Sending this information via email using Database Mail.
SQL Code:
if exists ()
begin
EXEC msdb.dbo.sp_send_dbmail
@profile_name = '',
@recipients = '',
@subject = '',
@body = '',
@execute_query_database = '',
@query = '',
@body_format='HTML',
@importance='High',
@attach_query_result_as_file=1,
@query_attachment_filename=''
end
else
print 'No Action
required'
In this script we used the system stored
procedure sp_send_dbmail to send mail notification with the required alert, let us navigate
through the parameters of this stored procedure:
@profile_name --> the name of
Database Mail profile already created.
@recipients --> the
list of users\groups that should receive the alert.
@subject --> the alert
Mail subject.
@body --> the alert
Mail body.
@execute_query_database --> the
target database
@query --> the SQL
statement used for generating alert details.
@body_format --> the format
of the email body “default value is HTML”
@importance --> the alert
mail importance “this should be high as an alert”
@attach_query_result_as_file --> default
value is 1 “attach result”
@query_attachment_filename --> “name
of text file contains result”
The final step here is to create a SQL Agent job
with the predefined execution interval based on the event type and details, the
SQL Agent job should then execute the alert script and send the alert mails to
the predefined recipients.
Next Step:
In the next post we will use this alerting model
for sending alerts in case of locking events as an example.
0 comments:
Post a Comment