How to send mail notification when SQL blocking occurs more than a defined time

Note: Best viewed in Internet Explorer 6 and above

Implementation:Sending mail alerts on SQL blocking queries details when particular query blocks other query by more than a defined time let say 2,3.. minutes.

Scenario:Lets say,there is one site where you have been deployed and you face blocking on SQL server all the time.Now your TL want you to send mail notification only when there is blocking more than 3 minutes.In this case,below queries will help you lot for concentrating on blocked queries and finding root blocking SPID.

Infrastructure: Below queries and processes are tested with SQL server2005,2008 R2,2012,2014.

Steps:Before implementing this on Live server lets simulate the blocking environment on an UAT server for your knowledge purpose.

First create a table in any user database on UAT server(No need to create this table on Live server) to simulate and understand blocking environment.

CREATE TABLE [dbo].[BlockingScene](
[name] [varchar](20) NULL,
[Add] [varchar](20) NULL,
[City] [varchar](20) NULL
) ON [PRIMARY]

Now populate above table with some dummy data.

insert into BlockingScene values(‘Dharmesh’,’Maharashtra’,’Mumbai’)

go 100000

Now open two query windows and put the below code in 1st and 2nd query windows resp and execute the queries one by one.

—–1st query window

use SqlDev
go
BEGIN TRANSACTION
select * from blockingScene WITH (TABLOCKx, HOLDLOCK)
waitfor delay ’00:55:00′ —will wait for 55 minutes.The format is ‘hh:mm:ss’
–release lock
commit transaction

—2nd query window

select * from blockingScene

Now run the sp_who2 active or below queries to see whether blocking is there.
Note:Please note that below query is filtered with where clause i.e. when blocking exceed with duration more than 3 minutes then it will show result.You can change the filter condition on basis of your business requirements.

select sp.spid,sp.blocked, sp.last_batch,sp.waittime,sp.waitresource,sp.lastwaittype,sp.cmd, DB_NAME(sp.dbid),
st.[text],sp.loginame,sp.hostname,sp.cpu
from sys.sysprocesses sp
cross apply sys.dm_exec_sql_text(sp.sql_handle) st
where sp.spid>50 and sp.spid<>sp.blocked and sp.blocked<>0 and datediff(mi,last_batch,GETDATE())>3

when the above condition true then the below screen shot you will get.

So the above process was to create a blocking environment on UAT server for understanding purpose.Now its time to create the stored procedure for sending email notification on sql blocking only when blocking exceed more than 3 minutes in my case.you can change it to any time as per client needs.
Use any database and create below table to capture blocking details.
Note:This is one time creation table.

CREATE TABLE [dbo].[CaptureBlockingDetails](

[spid] [smallint] NULL,
[VillainSpid] [smallint] NULL,
[last_batch] [datetime] NULL,
[CurrentTime] [datetime] default getdate() NOT NULL,
[waittime] [bigint] NULL,
[waitresource] [nchar](256) NULL,
[lastwaittype] [nchar](32) NULL,
[CmdStatement] [nchar](16) NULL,
[RunningQueries] [nvarchar](max) NULL,
[DatabaseName] [sysname] NOT NULL,
[LoginName] [varchar](30) NULL,
[HostName] [varchar](30) NULL,
[cpu] [int] NULL
) ON [PRIMARY]
GO

Now create stored procedure.

/*
Created by: Dharmesh Mishra
Date/Time:14th June 2015,12:00 PM
Like my fb page:www.facebook.com/sqlserverkillers
Visit my blogs:lapsql.wordpress.com
*/
USE [SqlDev] —use any system or user database
GO

/****** Object: StoredProcedure [dbo].[Usp_sendMailNotificationWhenBlockingGoesAboveThreeMinutes] Script Date: 06/14/2015 12:02:54 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

create procedure [dbo].[Usp_sendMailNotificationWhenBlockingGoesAboveThreeMinutes]
as
begin

————2nd insert value in created table for which is blocking more than 3 minutes————-
insert into dbo.CaptureBlockingDetails(spid ,
VillainSpid ,
last_batch ,
waittime,
waitresource ,
lastwaittype ,
CmdStatement ,
DatabaseName ,
RunningQueries,
LoginName,
HostName,
cpu

) select sp.spid,sp.blocked, sp.last_batch,sp.waittime,sp.waitresource,sp.lastwaittype,sp.cmd, DB_NAME(sp.dbid),
st.[text],sp.loginame,sp.hostname,sp.cpu
from sys.sysprocesses sp
cross apply sys.dm_exec_sql_text(sp.sql_handle) st
where sp.spid>50 and sp.spid<>sp.blocked and sp.blocked<>0 and datediff(mi,last_batch,GETDATE())>3
—final touch for sending mail notification when blocking duration goes above 3 Minutes
if (select top 1 DATEdiff(MI,last_batch,currenttime) from CaptureBlockingDetails where DATEdiff(MI,last_batch,currenttime)>3)>3

begin
print ‘CPU Alert Condition True, Sending Email..’DECLARE @tableHTML NVARCHAR(MAX) ;
SET @tableHTML =
N'<H1 bgcolor=”magenta”>Blocking Found</H1>’ +
N'<H2 bgcolor=”magenta”>SQL Server Session Details</H2>’ +
N'<table border=”1″>’ +
N'<tr bgcolor=”magenta”><th>spid</th><th>VillainSpid</th><th>last_batch</th><th>CurrentTime</th><th>waittime</th>’+
N'<th>waitresource</th><th>lastwaittype</th><th>CmdStatement</th><th>DatabaseName</th>’+
N'<th>RunningQueries</th><th>LoginName</th><th>HostName</th> <th>cpu</th></tr>’+

CAST ( ( SELECT distinct — or all by using *

td= spid,”,
td= VillainSpid,”,
td= last_batch,”,
td= CurrentTime,”,
td= waittime,”,
td= waitresource,”,
td= lastwaittype,”,
td= CmdStatement,”,
td= DatabaseName,”,
td= RunningQueries,”,
td= LoginName,”,
td= HostName,”,
td=cpu,”from CaptureBlockingDetails
FOR XML PATH(‘tr’), TYPE )AS NVARCHAR(MAX))+N'</table>’
— Change SQL Server Email notification code here
EXEC msdb.dbo.sp_send_dbmail
@recipients=’dharmesh@gmail.com’,
@profile_name = ‘DBA’,
@subject = ‘home:Blocking Found’,
@body = @tableHTML,@body_format = ‘HTML’;
END
— Truncate the Table
truncate Table CaptureBlockingDetails
end