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."
0 comments:
Post a Comment