Help: SQL Server

Sharing my knowlege about SQL Server Troubleshooting Skills

    Advertisements
  • Blog Stats

    • 1,616,836 hits
  • Select GETDATE()

    November 2014
    M T W T F S S
    « Sep   Dec »
     12
    3456789
    10111213141516
    17181920212223
    24252627282930
  • Advertisements

Archive for November 12th, 2014

Script : Setup email notification when blocking occurs

Posted by blakhani on November 12, 2014


How many times you have faced blocking situation and you are always late to take action. 

Recently one of my friend asked if there is anyway to get automatic email when blocking appears in SQL Server. There is no default mechanism in SQL Server to send such emails. I have asked my friend to create a SQL Agent Job. Here are the information which can be queried and notified.

  • How long blocking exists?
  • Which are the queries/SPs creating blocking?
  • What is the statement causing blocking.

What is needed to run below script?

  1. Database mail should be configured to send emails. In my example below I have a profile configured called “Database Mail” and I am sending mail to myself.
    1. Create Agent Job to run this script on periodic basis (5 min, 10 min or whatever you like)
/*
Discialmer:
The sample scripts are provided AS IS without warranty of any kind. The entire risk arising out of the use or performance of the sample scripts and documentation remains with you. 
In no event I shall be liable for any damages whatsoever (including, without limitation, damages for loss of business profits, business interruption, loss of business information, 
or other pecuniary loss) arising out of the use of or inability to use the sample scripts or documentation. */
SET NOCOUNT ON -- Checked for currenlty running queries by putting data in temp table SELECT s.session_id ,r.STATUS ,r.blocking_session_id ,r.wait_type ,wait_resource ,r.wait_time / (1000.0) 'WaitSec' ,r.cpu_time ,r.logical_reads ,r.reads ,r.writes ,r.total_elapsed_time / (1000.0) 'ElapsSec' ,Substring(st.TEXT, (r.statement_start_offset / 2) + 1, ( ( CASE r.statement_end_offset WHEN - 1 THEN Datalength(st.TEXT) ELSE r.statement_end_offset END - r.statement_start_offset ) / 2 ) + 1) AS statement_text ,Coalesce(Quotename(Db_name(st.dbid)) + N'.' + Quotename(Object_schema_name(st.objectid, st.dbid)) + N'.' + Quotename(Object_name(st.objectid, st.dbid)), '') AS command_text ,r.command ,s.login_name ,s.host_name ,s.program_name ,s.host_process_id ,s.last_request_end_time ,s.login_time ,r.open_transaction_count INTO #temp_requests FROM sys.dm_exec_sessions AS s INNER JOIN sys.dm_exec_requests AS r ON r.session_id = s.session_id CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) AS st WHERE r.session_id != @@SPID ORDER BY r.cpu_time DESC ,r.STATUS ,r.blocking_session_id ,s.session_id IF ( SELECT count(*) FROM #temp_requests WHERE blocking_session_id > 50 ) <> 0 BEGIN -- blocking found, sent email. DECLARE @tableHTML NVARCHAR(MAX); SET @tableHTML = N'<H1>Blocking Report</H1>' + N'<table border="1">' + N'<tr>' + N'<th>session_id</th>' + N'<th>Status</th>' + N'<th>blocking_session_id</th><th>wait_type</th><th>wait_resource</th>' + N'<th>WaitSec</th>' + N'<th>cpu_time</th>' + N'<th>logical_reads</th>' + N'<th>reads</th>' + N'<th>writes</th>' + N'<th>ElapsSec</th>' + N'<th>statement_text</th>' + N'<th>command_text</th>' + N'<th>command</th>' + N'<th>login_name</th>' + N'<th>host_name</th>' + N'<th>program_name</th>' + N'<th>host_process_id</th>' + N'<th>last_request_end_time</th>' + N'<th>login_time</th>' + N'<th>open_transaction_count</th>' + '</tr>' + CAST(( SELECT td = s.session_id ,'' ,td = r.STATUS ,'' ,td = r.blocking_session_id ,'' ,td = r.wait_type ,'' ,td = wait_resource ,'' ,td = r.wait_time / (1000.0) ,'' ,td = r.cpu_time ,'' ,td = r.logical_reads ,'' ,td = r.reads ,'' ,td = r.writes ,'' ,td = r.total_elapsed_time / (1000.0) ,'' ,td = Substring(st.TEXT, (r.statement_start_offset / 2) + 1, ( ( CASE r.statement_end_offset WHEN - 1 THEN Datalength(st.TEXT) ELSE r.statement_end_offset END - r.statement_start_offset ) / 2 ) + 1) ,'' ,td = Coalesce(Quotename(Db_name(st.dbid)) + N'.' + Quotename(Object_schema_name(st.objectid, st.dbid)) + N'.' + Quotename(Object_name(st.objectid, st.dbid)), '') ,'' ,td = r.command ,'' ,td = s.login_name ,'' ,td = s.host_name ,'' ,td = s.program_name ,'' ,td = s.host_process_id ,'' ,td = s.last_request_end_time ,'' ,td = s.login_time ,'' ,td = r.open_transaction_count FROM sys.dm_exec_sessions AS s INNER JOIN sys.dm_exec_requests AS r ON r.session_id = s.session_id CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) AS st WHERE r.session_id != @@SPID AND blocking_session_id > 0 ORDER BY r.cpu_time DESC ,r.STATUS ,r.blocking_session_id ,s.session_id FOR XML PATH('tr') ,TYPE ) AS NVARCHAR(MAX)) + N'</table>'; EXEC msdb.dbo.sp_send_dbmail @body = @tableHTML ,@body_format = 'HTML' ,@profile_name = N'Database Mail' ,@recipients = N'blakhani@outlook.com' ,@Subject = N'Blocking Detected' END DROP TABLE #temp_requests

Here is the sample email which I received when I caused blocking and executed the script.

if you want to use it, you need to create a SQL Agent Job which would run the script on periodic basics and send email.

Please modify as per your requirement/environment.

  • Cheers,
  • Balmukund Lakhani
  • Twitter @blakhani
  • Author: SQL Server 2012 AlwaysOnPaperback, Kindle
  • Advertisements

    Posted in Script, SQL Server | Tagged: , , , , , | 28 Comments »