Blocking Alert -Setup

USE [DBA]

GO

/****** Object:  StoredProcedure [dbo].[BlockingAlert]    Script Date: 18/02/2016 13:09:57 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

--select * from BlockingMonitoring

--USE [DBA]

--GO

--select @@SERVERNAME

--[BlockingAlert] 'KCRCSTVSQL410\I01'

ALTER procedure [dbo].[BlockingAlert]

(

@datasource sysname

)

As begin

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[BlockingMonitoring]') AND type in (N'U'))

BEGIN

CREATE TABLE [dbo].[BlockingMonitoring](

       [WaitInSeconds] [bigint] NULL,

       [BlockingSessionId] [int] NULL,

       [BlockingUser] [nvarchar](128) NOT NULL,

       [BlockingSQL] [nvarchar](max) NULL,

       [WhyBlocked] [nvarchar](60) NULL,

       [BlockedSessionId] [smallint] NULL,

       [BlockedUser] [nvarchar](128) NULL,

       [BlockedSQL] [nvarchar](max) NULL,

       [DatabaseName] [nvarchar](128) NULL,

       [Programname] [nvarchar](32) NULL,

       [datereceived] date null

) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

END

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[BlockingAnalysis]') AND type in (N'U'))

BEGIN

CREATE TABLE [dbo].[BlockingAnalysis](

       [WaitInSeconds] [bigint] NULL,

       [BlockingSessionId] [int] NULL,

       [BlockingUser] [nvarchar](128) NOT NULL,

       [BlockingSQL] [nvarchar](max) NULL,

       [WhyBlocked] [nvarchar](60) NULL,

       [BlockedSessionId] [smallint] NULL,

       [BlockedUser] [nvarchar](128) NULL,

       [BlockedSQL] [nvarchar](max) NULL,

       [DatabaseName] [nvarchar](128) NULL,

       [Programname] [nvarchar](32) NULL,

       [datereceived] date null

) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

END

SELECT

Waits.wait_duration_ms / 1000 AS WaitInSeconds,

Blocking.session_id as BlockingSessionId

, Sess.login_name AS BlockingUser

, BlockingSQL.text AS BlockingSQL

, Waits.wait_type WhyBlocked

, Blocked.session_id AS BlockedSessionId

, USER_NAME(Blocked.user_id) AS BlockedUser

, BlockedSQL.text AS BlockedSQL

, DB_NAME(Blocked.database_id) AS DatabaseName

,Sess.client_interface_name as Programname

into #blockingAnalysis

FROM sys.dm_exec_connections AS Blocking

INNER JOIN sys.dm_exec_requests AS Blocked

ON Blocking.session_id = Blocked.blocking_session_id

INNER JOIN sys.dm_os_waiting_tasks AS Waits

ON Blocked.session_id = Waits.session_id

RIGHT OUTER JOIN sys.dm_exec_sessions Sess

ON Blocking.session_id = sess.session_id

CROSS APPLY sys.dm_exec_sql_text(Blocking.most_recent_sql_handle)

AS BlockingSQL

CROSS APPLY sys.dm_exec_sql_text(Blocked.sql_handle) AS BlockedSQL

ORDER BY BlockingSessionId, BlockedSessionId

select * from #blockingAnalysis

-- is currently any blocking ...if it's no then proceed below step

if @@rowcount=0

Begin

select 'Spid' +' '+cast(B.BlockedSessionId as varchar)+' '+'released by'+' '+cast(B.BlockingSessionId as varchar) as BlockingStatus,B.*

into #blockingrelease

 from [BlockingMonitoring] as B

Left outer join #blockingAnalysis as BA

on BA.BlockingSessionId=B.BlockingSessionId and BA.BlockedSessionId=B.BlockedSessionId

where BA.BlockingSessionId is null and BA.BlockedSessionId is null and B.WaitInSeconds>=120

--is it latest blocking resolved and released ? if it's yes then proceed the below step

if @@rowcount>0

Begin

DECLARE @bodyMsg nvarchar(max)

DECLARE @subject nvarchar(max)

DECLARE @tableHTML nvarchar(max)

Declare @srvname sysname

select @srvname=srvname from sys.sysservers where datasource=@datasource

SET @subject = 'Blocking automatically resolved' +'  '+ 'in' + '  ' + @srvname

--set @bodyMsg='Please contact DBA team to resolve this Blocking issue'

SET @tableHTML =

N''+   

N'

Blocking Alert

' +

N'' +

N'

BlockingStatus

WaitInSeconds     

BlockingSessionId 

BlockingUser       

BlockingSQL              

WhyBlocked               

BlockedSessionId  

BlockedUser              

BlockedSQL               

DatabaseName       

Programname              

' +

CAST ( (

SELECT td = CAST(BlockingStatus AS VARCHAR(100)),'',

td = CAST(WaitInSeconds AS VARCHAR(100)),'',

td = CAST(BlockingSessionId AS VARCHAR(100)),'',

td = CAST(BlockingUser AS VARCHAR(100)),'',

td = CAST(BlockingSQL AS VARCHAR(100)),'',

td = CAST(WhyBlocked AS VARCHAR(100)),'',

td = CAST(BlockedSessionId AS VARCHAR(100)),'',

td = CAST(BlockedUser AS VARCHAR(100)),'',

td = CAST(BlockedSQL AS VARCHAR(100)),'',

td = CAST(DatabaseName AS VARCHAR(100)),'',

td = CAST(Programname AS VARCHAR(100))

FROM #blockingrelease

FOR XML PATH('tr'), TYPE

) AS NVARCHAR(MAX) ) +

N'

'

+     

N'

Blocking Status - Resolved Automatically

'

EXEC msdb.dbo.sp_send_dbmail

@profile_name='db_profile name',

@recipients='xxxxxxx',

--@copy_recipients ='xxxxxx',

@subject = @subject1,

@body = @tableHTML1,

@body_format = 'HTML' ;

Delete from  [BlockingMonitoring] output DELETED.* INTO [BlockingAnalysis] where [WaitInSeconds]>=120

Drop table #blockingrelease

End

else

print 'validation check 2 : No Release Email Alert'

End

Else

print 'validation check 1 :No Release Email Alert'

drop table #blockingAnalysis

--for releasing the spid and Analysis purpose

INSERT INTO [dbo].[BlockingMonitoring]

           ([WaitInSeconds]

           ,[BlockingSessionId]

           ,[BlockingUser]

           ,[BlockingSQL]

           ,[WhyBlocked]

           ,[BlockedSessionId]

           ,[BlockedUser]

           ,[BlockedSQL]

           ,[DatabaseName]

           ,[Programname]

                 ,[datereceived])

SELECT

Waits.wait_duration_ms / 1000 AS WaitInSeconds,

Blocking.session_id as BlockingSessionId

, Sess.login_name AS BlockingUser

, BlockingSQL.text AS BlockingSQL

, Waits.wait_type WhyBlocked

, Blocked.session_id AS BlockedSessionId

, USER_NAME(Blocked.user_id) AS BlockedUser

, BlockedSQL.text AS BlockedSQL

, DB_NAME(Blocked.database_id) AS DatabaseName

,Sess.client_interface_name as Programname

,getdate() as datereceived

FROM sys.dm_exec_connections AS Blocking

INNER JOIN sys.dm_exec_requests AS Blocked

ON Blocking.session_id = Blocked.blocking_session_id

INNER JOIN sys.dm_os_waiting_tasks AS Waits

ON Blocked.session_id = Waits.session_id

RIGHT OUTER JOIN sys.dm_exec_sessions Sess

ON Blocking.session_id = sess.session_id

CROSS APPLY sys.dm_exec_sql_text(Blocking.most_recent_sql_handle)

AS BlockingSQL

CROSS APPLY sys.dm_exec_sql_text(Blocked.sql_handle) AS BlockedSQL

ORDER BY BlockingSessionId, BlockedSessionId

SELECT

Waits.wait_duration_ms / 1000 AS WaitInSeconds,

Blocking.session_id as BlockingSessionId

, Sess.login_name AS BlockingUser

, BlockingSQL.text AS BlockingSQL

, Waits.wait_type WhyBlocked

, Blocked.session_id AS BlockedSessionId

, USER_NAME(Blocked.user_id) AS BlockedUser

, BlockedSQL.text AS BlockedSQL

, DB_NAME(Blocked.database_id) AS DatabaseName

,Sess.client_interface_name as Programname

into #backupmonitoring

FROM sys.dm_exec_connections AS Blocking

INNER JOIN sys.dm_exec_requests AS Blocked

ON Blocking.session_id = Blocked.blocking_session_id

INNER JOIN sys.dm_os_waiting_tasks AS Waits

ON Blocked.session_id = Waits.session_id

RIGHT OUTER JOIN sys.dm_exec_sessions Sess

ON Blocking.session_id = sess.session_id

CROSS APPLY sys.dm_exec_sql_text(Blocking.most_recent_sql_handle)

AS BlockingSQL

CROSS APPLY sys.dm_exec_sql_text(Blocked.sql_handle) AS BlockedSQL

ORDER BY BlockingSessionId, BlockedSessionId

select WaitInSeconds from #backupmonitoring where WaitInSeconds>=120

if @@ROWCOUNT>0

begin

DECLARE @bodyMsg1 nvarchar(max)

DECLARE @subject1 nvarchar(max)

DECLARE @tableHTML1 nvarchar(max)

Declare @srvname1 sysname

select @srvname1=srvname from sys.sysservers where datasource=@datasource

SET @subject1 = 'Blocking Alert' +'  '+ 'in' + '  ' + @srvname1

SET @tableHTML1 =

N''+   

N'

Blocking Alert

' +

N'' +

N'

WaitInSeconds     

BlockingSessionId 

BlockingUser       

BlockingSQL              

WhyBlocked               

BlockedSessionId  

BlockedUser              

BlockedSQL               

DatabaseName       

Programname              

' +

CAST ( (

SELECT td = CAST(WaitInSeconds AS VARCHAR(100)),'',

td = CAST(BlockingSessionId AS VARCHAR(100)),'',

td = CAST(BlockingUser AS VARCHAR(100)),'',

td = CAST(BlockingSQL AS VARCHAR(100)),'',

td = CAST(WhyBlocked AS VARCHAR(100)),'',

td = CAST(BlockedSessionId AS VARCHAR(100)),'',

td = CAST(BlockedUser AS VARCHAR(100)),'',

td = CAST(BlockedSQL AS VARCHAR(100)),'',

td = CAST(DatabaseName AS VARCHAR(100)),'',

td = CAST(Programname AS VARCHAR(100))

FROM #backupmonitoring

FOR XML PATH('tr'), TYPE

) AS NVARCHAR(MAX) ) +

N'

'

+     

N'

Please contact DBA team to resolve this Blocking issue

'

EXEC msdb.dbo.sp_send_dbmail

@profile_name='db_profile name',

@recipients='xxxxxxx',

--@copy_recipients ='xxxxxx',

@subject = @subject1,

@body = @tableHTML1,

@body_format = 'HTML' ;

End

else

print 'No Blocking Alert Email'

Drop table #backupmonitoring

End