Tagged: SQL 2015

Create Blocking Alert – SQL 2005 , SQL 2008 R2 and SQL 2012

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

Advertisements