We often need to create blocking alert in our Production server to capture existing blocking on server and keep record for future analysis.
Here are steps which help us to create blocking alerts-
Either you can receive blocking details through e-mail or you can log event id in application log and configure your monitoring tool to capture from application log.
STEP 1 – Create SP to capture blocking details and generate e-mail\ event id in application log.
Note – and datediff(minute, last_batch, getdate()) > 15, through which you can define blocking duration to generate e-mail or event, I am using 15 min
USE [db]
go
/****** Object: StoredProcedure [dbo].[usp_checkForLongRunningBlockers_log] Script Date: 5/18/2015 9:22:50 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE procedure [dbo].[usp_checkForLongRunningBlockers_log]
as
declare @@leadblocker_spid smallint
declare @@dbname nvarchar(128)
declare @@login_time datetime
declare @@last_batch datetime
declare @@open_tran smallint
declare @@status nchar(30)
declare @@hostname nchar(128)
declare @@program_name nchar(128)
declare @@cmd nchar(16)
declare @@loginame nchar(128)
declare @@net_address nchar(12)
declare @@net_library nchar(12)
declare @@string varchar(2000)
select
@@leadblocker_spid = sp.spid,
@@dbname = db_name(sp.dbid),
@@login_time = login_time,
@@last_batch = last_batch,
@@open_tran = open_tran,
@@status = sp.status,
@@hostname = hostname,
@@program_name = program_name,
@@cmd = cmd,
@@loginame = loginame,
@@net_address = net_address,
@@net_library = net_library
FROM master.dbo.sysprocesses sp
LEFT JOIN sys.dm_exec_requests er
ON er.session_id = sp.spid
OUTER APPLY sys.dm_exec_sql_text(er.sql_handle) AS qt
WHERE spid IN (SELECT blocked FROM master.dbo.sysprocesses)
AND blocked = 0
and datediff(minute, last_batch, getdate()) > 15
set @@string =
‘Blocking Details -Date: ‘ + convert(varchar(20), getdate(), 9) + ‘
@@leadblocker_spid = ‘ + convert(varchar(5),@@leadblocker_spid) + ‘
@@dbname = ‘ + @@dbname + ‘
@@login_time = ‘ + convert(varchar(20), @@login_time, 9) + ‘
@@last_batch = ‘ + convert(varchar(20), @@last_batch, 9) + ‘
@@open_tran = ‘ + convert(varchar(5), @@open_tran) + ‘
@@status = ‘ + @@status + ‘
@@hostname = ‘ + @@hostname + ‘
@@program_name = ‘ + @@program_name + ‘
@@cmd = ‘ + @@cmd + ‘
@@loginame = ‘ + @@loginame + ‘
@@net_address = ‘ + @@net_address + ‘
@@net_library = ‘ + @@net_library + ”
if @@leadblocker_spid is null
return
else
–exec dbo.sp_SQLSMTPMail_html
— @vcTo = ‘nshukla@xyz.com’,
— @vcBody = @@string,
— @vcSubject = ‘Long Running Blocking’,
— @vcAttachments = null,
— @vcQuery = null,
— @vcFrom = ‘noreply@xyz.com’,
— @vcCC = ”,
— @vcBCC = ”,
— @vcSMTPServer = ‘nav-mail2.xyz.com’, — put local network smtp server name here
— @cSendUsing = ‘2’,
— @vcPort = ’25’,
— @cAuthenticate = ‘0’,
— @vcDSNOptions = ‘0’,
— @vcTimeout = ’30’,
— @vcSenderName = null,
— @vcServerName = null
EXEC xp_logevent 60000, @@string, error
GO
STEP 2 – Create table to capture blocking history –
USE [db]
GO
/****** Object: Table [dbo].[BLOCKING_HISTORY] Script Date: 5/18/2015 9:26:40 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[BLOCKING_HISTORY](
[spid] [smallint] NULL,
[kpid] [smallint] NULL,
[blocked] [smallint] NULL,
[waittype] [varbinary](5000) NULL,
[waittime] [bigint] NULL,
[lastwaittype] [nvarchar](500) NULL,
[waitresource] [nvarchar](500) NULL,
[dbid] [smallint] NULL,
[uid] [smallint] NULL,
[cpu] [int] NULL,
[physical_io] [bigint] NULL,
[memusage] [int] NULL,
[login_time] [datetime] NULL,
[last_batch] [datetime] NULL,
[ecid] [smallint] NULL,
[open_tran] [smallint] NULL,
[status] [nvarchar](500) NULL,
[hostname] [nvarchar](500) NULL,
[program_name] [nvarchar](500) NULL,
[hostprocess] [nvarchar](500) NULL,
[cmd] [nvarchar](500) NULL,
[nt_domain] [nvarchar](500) NULL,
[nt_username] [nvarchar](500) NULL,
[net_address] [nvarchar](500) NULL,
[net_library] [nvarchar](500) NULL,
[loginame] [nvarchar](500) NULL,
[context_info] [varbinary](5000) NULL,
[sql_handle] [varbinary](5000) NULL,
[stmt_start] [int] NULL,
[stmt_end] [int] NULL,
[request_id] [int] NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
STEP 3 – Create SP to INSERT blocking details in Blocking History table.
USE [db]
GO
/****** Object: StoredProcedure [dbo].[usp_blockingEntry] Script Date: 5/18/2015 9:25:52 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE Procedure [dbo].[usp_blockingEntry]
as
begin
SET NOCOUNT ON
declare @blockingcount int = ( select count(1) from sys.sysprocesses where blocked 0 )
if @blockingcount > 0
INSERT INTO [dbo].[BLOCKING_HISTORY]
( spid
, kpid
, blocked
, waittype
, waittime
, lastwaittype
, waitresource
, dbid
, uid
, cpu
, physical_io
, memusage
, login_time
, last_batch
, ecid
, open_tran
, status
–, sid
, hostname
, program_name
, hostprocess
, cmd
, nt_domain
, nt_username
, net_address
, net_library
, loginame
, context_info
, sql_handle
, stmt_start
, stmt_end
, request_id
)
select
spid
, kpid
, blocked
, waittype
, waittime
, lastwaittype
, waitresource
, dbid
, uid
, cpu
, physical_io
, memusage
, login_time
, last_batch
, ecid
, open_tran
, status
–, sid
, hostname
, program_name
, hostprocess
, cmd
, nt_domain
, nt_username
, net_address
, net_library
, loginame
, context_info
, sql_handle
, stmt_start
, stmt_end
, request_id
from sys.sysprocesses where blocked 0
end
GO
STEP – 4 – Create SP to get lead blocker –
USE [db]
GO
/****** Object: StoredProcedure [dbo].[usp_leadblocker] Script Date: 5/18/2015 9:44:39 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create procedure [dbo].[usp_leadblocker]
as
select *
from
(
select
‘Blocking’ as State,
hostname as host,
db_name(dbid) as dbname,
SPID,
blocked,
lastwaittype,
dbid,
login_time,
last_batch,
open_tran,
status,
hostname,
program_name,
cmd,
nt_domain,
nt_username
from sys.sysprocesses (nolock)
where
spid in (
select distinct blocked
from sys.sysprocesses (nolock)
where
blocked 0
and blocked spid
)
and blocked = 0
and blocked spid
union all
select
‘Blocked’ as State,
hostname as host,
db_name(dbid) as dbname,
SPID,
blocked,
lastwaittype,
dbid,
login_time,
last_batch,
open_tran,
status,
hostname,
program_name,
cmd,
nt_domain,
nt_username
from sys.sysprocesses (nolock)
where
blocked 0
and blocked spid
) dt
order by 1 desc
GO
STEP 5 – T- SQL to check SPID text.
DECLARE @sqltext VARBINARY(128)
SELECT @sqltext = sql_handle
FROM sys.sysprocesses
WHERE spid = 64
SELECT TEXT
FROM sys.dm_exec_sql_text(@sqltext)
GO
***************************************
DECLARE @sqltext VARBINARY(128)
SELECT @sqltext = sql_handle
FROM sys.sysprocesses
WHERE spid = 64
SELECT TEXT
FROM ::fn_get_sql(@sqltext)
GO
*****************************
DBCC INPUTBUFFER(64)
GO
*************************
SP_WHO2 64
go
Learn Sql Team 