SQL server check chain blocking
The below script is what I am using to check for blocking chain of SQL connections. I run the below script as a SQL scheduled task. Whenever there is a blocking during the job run, it will send an email to the administrator. The below script is verified to be able to run on SQL2000
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
declare @spid int,
@blocked int,
@dbid int,
@status varchar(50),
@waittime int,
@cpu int,
@loginname varchar (50),
@hostname varchar (50),
@program_name varchar(50),
@cmd varchar (500),
@eventinfo varchar (1000),
@msg varchar (5000),
@db varchar (50)
--drop table #procs
create table #procs (eventtype varchar(60),parameters int, eventinfo varchar (600))
declare CUR1 cursor for
select spid, blocked,dbid, status,waittime,
cpu,loginame, hostname, program_name
from sysprocesses where blocked >0 and waittime > 2000
open CUR1
fetch next from CUR1 into @spid, @blocked, @dbid, @status, @waittime, @cpu, @loginname, @hostname, @program_name
set @msg = ''
while @@fetch_status = 0
BEGIN
select @db = name from sysdatabases where dbid = @dbid
truncate table #procs
set @cmd = (select 'dbcc inputbuffer(' + cast(@spid as varchar) + ')')
insert into #procs (eventtype, parameters, eventinfo)
exec (@cmd)
select @eventinfo =
eventinfo from #procs
set @msg = @msg + ' Host (Blocking) = ' + @hostname + '
Database = ' + @db + '
login = ' + @loginname + '
Blocking spid= ' + CAST (@spid as varchar) + '
Query = ' + @eventinfo + '
'
Database = ' + @db + '
login = ' + @loginname + '
Blocking spid= ' + CAST (@spid as varchar) + '
Query = ' + @eventinfo + '
'
truncate table #procs
set @cmd = (select 'dbcc inputbuffer(' + cast(@blocked as varchar) + ')')
insert into #procs (eventtype, parameters, eventinfo)
exec (@cmd)
select @eventinfo =
eventinfo from #procs
select @hostname = hostname from sysprocesses where spid = @blocked
select @loginname = loginame from sysprocesses where spid = @blocked
set @msg = @msg + '; Wait time =' + cast (@waittime as varchar) + '; Host (Blocked) =
' + @hostname + '; login = ' + @loginname + '; Blocked spid = ' + cast (@blocked as varchar) + '; Query = ' + @eventinfo + '
'
'
fetch next from CUR1 into @spid, @blocked, @dbid, @status, @waittime, @cpu, @loginname, @hostname, @program_name
END
CLOSE CUR1
DEALLOCATE CUR1
IF @msg <> ''
begin
exec msdb.dbo.Sp_send_dbmail @recipients = @Recivers,@body = @msg,@subject = @sub; --or any send email command you like to use
end
Comments