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 + '
'
    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

Popular Posts