Making Your Own Alerts (Part 2)

In the previous post "Making Your Own Alerts (Part 1)" we talked about how to create a customized alert for any event inside SQL Server beside the default alerting feature in SQL DB engine, today will make a simple example for this and our target will be sending alerts via email in case of locking cases occur in our databases.

As we mentioned in the last post, we have to set a condition for our alert, a condition that based on it the alert should be fired and sent to DBAs, in our case the condition here will be related to blocking_session_id andwait_time column’s values in sys.dm_exec_requests DMV.

We talked before about sys.dm_exec_requests DMV in a previous post

“Collecting Locking Information” and also gave a brief description about the

blocking_session_id and wait_time columns, then the alerting values for these two columns in our case should be:

blocking_session_id > 0 --> this indicates that there is a blocking on this session and define the session id for the blocking session

(wait_time/1000)/60 >2) --> this indicates the locking duration, in our example we assume that a locking with less than 2 minutes should be ignored but it is depend on the DBAs point of view and also on the system criticality.

After defining our condition then will move to the next step which is sending the alerting mail with the alert details, we will explain here the SQL statement that contains the alert details as we already discussed the alert sending parameters in the previous post.

-- Query

SELECT @@servername as 'sql_instance',session_id AS spid,blocking_session_id,

db_name(database_id) as 'database_name',(wait_time/1000)/60 as 'wait_time (m)'

FROM sys.dm_exec_requests

WHERE blocking_session_id > 0 and (wait_time/1000)/60>15

ORDER BY wait_time DESC

So we are selecting the SQL Server instance name, the blocked session id, the blocking session id, the blocked database name and finally the locking duration in minutes ordered by the locking duration in case we have multiple locking events at the same time, this information will be sent in the alert email as an attachment to be reviewed by the DBAs in order to start taking the appropriate actions.

We can notice that the information sent here is little bit less than the detailed information we got before while collecting locking information, correct!!, and this is simply because we just need to send the "basic" information required for alerting DBAs with the event itself, then they can start analyzing the current locking events and collect all sufficient information, also it is better to combine the locking alert job with the locking collector one in order to have all required information ready, this may reduce the analysis time you spent after getting notified with the locking events.

This is the complete script:

IF exists (SELECT 1 FROM sys.dm_exec_requests WHERE  blocking_session_id > 0

and (wait_time/1000)/60 >5)

BEGIN

EXEC msdb.dbo.sp_send_dbmail

@profile_name = 'SQL Server Database Mail Profile Name',

@recipients = 'Mail address for the recipients',

@subject = 'Define mail subject',

@body = 'Define mail body',

@execute_query_database = 'target database',

@query = 'SELECT @@servername as ''sql_instance'',

session_id AS spid,

blocking_session_id,

db_name(database_id) as ''database_name'',

(wait_time/1000)/60 as ''wait_time (m)''

FROM sys.dm_exec_requests

WHERE blocking_session_id > 0 and (wait_time/1000)/60>15

ORDER BY wait_time DESC',

@body_format='HTML',

@importance='High',

@attach_query_result_as_file=1,

@query_attachment_filename='locking.txt'

END

ELSE

PRINT 'No Locking'

The final step is creating a SQL agent job for this script and scheduling it to run with a low interval "supposed to be equal or little bit greater than the wait time condition in the SQL script."