SQL Server – T-SQL Script to detect blocking & block chain(s) and send alerts for it with detailed report
Today I’ll share a script, which I
believe most of us will find very useful for their environment just like me.
Blocking is very common event on a database server and in-fact blocking occurs
almost all the time and fortunately most of them are not harmful (enough). But
as we all know this normal occurring event can sometime cause huge issues
and we should have some mechanism to track these harmful ones.
Generally without third party
assistance it is bit difficult to actually isolate blocking, which can be
potentially harmful right now or in future and the default alerting options can
flood inbox with too many mail alerts suppressing the one's which are relevant. Fortunately I’m able to ease the things
a bit and was able to find a T-SQL way to track and send report of potential
problem causing blocking and block chain processes and that too without flooding
inbox with non-relevant alerts.
--
=============================================
USE [master]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
--
=============================================
-- Author: Siddharth Chauhan
-- Description: This SP will get the details of blocked and blocking queries.
--
=============================================
CREATE PROCEDURE [dbo].[usp_get_blocked_process_report]
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
declare @spid int, @spid2 int, @body_text nvarchar(1000), @query_text nvarchar(max), @count int, @cmd nvarchar(max)
create table #t1 (spid int,blocked int)
select @cmd = N'select distinct spid,
blocked from sys.sysprocesses (nolock) where blocked!=0 and blocked !=spid'
insert into #t1
exec sp_executesql @cmd
waitfor delay '00:01:00'
create table #t2 (spid int,blocked int)
insert into #t2
select distinct spid, blocked from sys.sysprocesses (nolock)
where blocked!=0 and blocked !=spid
select @count = (select COUNT(isnull(blocked,0)) from #t2 where blocked in (select blocked from #t1))
If @count <= 0 or @count is null
return;
declare cur_proc cursor fast_forward for
select distinct blocked from sys.sysprocesses (nolock) where blocked!=0 and blocked !=spid
open cur_proc
fetch next from cur_proc into @spid
while @@FETCH_STATUS = 0
begin
if not exists(select 1 from sys.sysprocesses (nolock) where blocked!=0 and spid = @spid)
begin
select @spid2 = (select top 1 spid from sys.sysprocesses(nolock) where blocked = @spid)
select @body_text = N'A block chain event has
occurred on server ARLMSININSQL01 at ' +
convert(nvarchar(30),getdate(),13)
+ '. Details for the same has
been attached.'
select @query_text = N'exec
msdb.dbo.sp_send_dbmail @profile_name= N''ARLMSININSQL01'', @recipients =
N''DB_Team@eclerx.com'',
@subject
= N''Block Chain Alert - ARLMSININSQL01'', @importance = ''High'', @body = ''' + @body_text + ''',
@query
= '' set nocount on
print ''''Total number of blocked processes on
server:''''
print
''''=========================================''''
print '''' ''''
select @@servername
"server_name",COUNT(1) "number_of_blocked_processes" from
sys.sysprocesses where blocked!=0
print '''' ''''
print ''''=========================================''''
print
''''=========================================''''
print '''' ''''
print '''' ''''
print ''''Originator Query Details are as
follows:''''
print
''''=========================================''''
print ''''Process details:''''
print '''' ''''
exec sp_who2 ' + CONVERT(nvarchar(4),@spid) +'
print '''' ''''
print
''''=========================================''''
print ''''Following Query has been fired:''''
print '''' ''''
dbcc inputbuffer(' +
CONVERT(nvarchar(4),@spid) +')
print '''' ''''
print
''''=========================================''''
print
''''=========================================''''
print '''' ''''
print '''' ''''
print ''''Blocked Query Details are as
follows:''''
print
''''=========================================''''
print ''''Process details:''''
print '''' ''''
exec sp_who2 ' + CONVERT(nvarchar(4),@spid2) +'
print '''' ''''
print
''''=========================================''''
print ''''Following Query has been fired:''''
print '''' ''''
dbcc inputbuffer(' +
CONVERT(nvarchar(4),@spid2) +')
print '''' ''''
print
''''=========================================''''
print
''''========================================='''''',
@attach_query_result_as_file
= 1, @query_attachment_filename = ''Blocked_and_Blocking_Query_Details.txt'''
--print @query_text
exec sp_executesql @query_text
end
fetch next from cur_proc into @spid
end
close cur_proc
deallocate cur_proc
END
GO
--
=============================================
The main thing in this script which
helps in controlling the mail flood is rechecking of blocked processes after a
delay. This helps in avoiding report of blocking which lasts only for few
seconds. I’ve used a delay of 30 seconds, however it can be increased or
decreased as per the acceptable level for your environment.
Another important
thing to note here is that this script will send one mail for each block chain
providing details about the root process of the block chain and the immediate
next process. Of-course we need to schedule it in a job for continuous tracking\monitoring and frequency of the job again will be as per the environment requirements.
Hope you’ll find this script
useful. Please don’t forget to share and leave your valued feedback\comments.
Comments
Post a Comment