Making Your Own Alerts (Part 1)

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.