Monitor blocking in SQL70

				
--**************************************
-- Name: Monitor blocking in SQL70
-- Description:This utility SP was taken from a KB article on how to monitor blocking in SQL70. You can find the KB article at http://support.microsoft.com/view/dev.asp?kb=251004
-- By: Umachandar
--
--This code is copyrighted and has-- limited warranties.Please see http://www.Planet-Source-Code.com/vb/scripts/ShowCode.asp?txtCodeId=142&lngWId=5--for details.--**************************************

use master
go
if exists (select * from sysobjects where id = object_id('dbo.sp_blocker_pss') and sysstat & 0xf = 4)
drop procedure dbo.sp_blocker_pss
GO
create proc sp_blocker_pss (@fast int = 0)
as 
set nocount on
declare @spid varchar(6), @uid varchar(6), @blocked varchar(6)
declare @tmpchar varchar(255)
declare @time datetime
select @spid = count(*) from master..sysprocesses where blocked!=0 or waittype != 0x0000
if @spid > 0
begin
select @time = getdate()
select @tmpchar='Start time: ' + convert(varchar(26), @time, 113)
print @tmpchar
print ' '
if (@fast = 1)
begin
 select spid, status, blocked, cmd, lastwaittype,
 open_tran,waitresource, waittype, waittime, cpu, physical_io,
 memusage,last_batch=convert(varchar(26), last_batch,113),
 login_time=convert(varchar(26), login_time,113), net_address,
 net_library,dbid, ecid, kpid, hostname,hostprocess,
 loginame,program_name, nt_domain, nt_username, uid, sid
 from master..sysprocesses
 where blocked!=0 or waittype != 0x0000 or spid in (select blocked from master..sysprocesses where blocked != 0)
 Print 'Blocking Lock Information:'
 Print '=========================='
 Print''
 select convert (smallint, req_spid) As spid, 
 rsc_dbid As dbid,
 rsc_objid As ObjId,
 rsc_indid As IndId,
 substring (v.name, 1, 4) As Type,
 substring (rsc_text, 1, 16) as Resource,
 substring (u.name, 1, 8) As Mode,
 substring (x.name, 1, 5) As Status
 from master.dbo.syslockinfo s,
 master.dbo.spt_values v,
 master.dbo.spt_values x,
 master.dbo.spt_values u
 where s.rsc_type = v.number
 and v.type = 'LR'
 and s.req_status = x.number
 and x.type = 'LS'
 and s.req_mode + 1 = u.number
 and u.type = 'L'
 and s.rsc_text in (select rsc_text from syslockinfo where req_status <> 1)
 and s.req_status = 1
 --order by spid
 Print''
 Print 'Waiting Lock Information:'
 Print '=========================='
 Print''
 select 	convert (smallint, req_spid) As spid, 
 rsc_dbid As dbid,
 rsc_objid As ObjId,
 rsc_indid As IndId,
 substring (v.name, 1, 4) As Type,
 substring (rsc_text, 1, 16) as Resource,
 substring (u.name, 1, 8) As Mode,
 substring (x.name, 1, 5) As Status
 from master.dbo.syslockinfo s,
 master.dbo.spt_values v,
 master.dbo.spt_values x,
 master.dbo.spt_values u
 wheres.rsc_type = v.number
 and v.type = 'LR'
 and s.req_status = x.number
 and x.type = 'LS'
 and s.req_mode + 1 = u.number
 and u.type = 'L'
 and s.req_status <> 1
-- order by spid
end -- fast set
else 
begin -- Fast not set
 select spid, status, blocked, cmd, lastwaittype, open_tran, 
 waitresource, waittype, waittime, cpu, physical_io, 
 memusage, last_batch=convert(varchar(26), last_batch,113), login_time=convert(varchar(26),login_time,113), 
 net_address, net_library, dbid, ecid, kpid, hostname, hostprocess, loginame,
 program_name, nt_domain, nt_username, uid, sid
 from master..sysprocesses
 print ''
 print 'SPIDs at the head of blocking chains'
 select spid from master..sysprocesses 
 where blocked = 0 and spid in (select blocked from master..sysprocesses where spid != 0)
 print ''
 exec sp_lock
end -- Fast not set
dbcc traceon(3604)
Print ''
Print ''
Print '*********************************************************************'
Print 'Print out DBCC Input buffer for all blocked or blocking spids.'
Print 'Print out DBCC PSS info only for SPIDs at the head of blocking chains'
Print '*********************************************************************'
declare ibuffer insensitive cursor for
select cast (spid as varchar(6)) as spid, cast (uid as varchar(6)) as uid, cast (blocked as varchar(6)) as blocked
from master..sysprocesses 
where (spid <> @@spid) and (blocked!=0 or waittype != 0x0000 or spid in (select blocked from master..sysprocesses where blocked != 0))
open ibuffer
fetch next from ibuffer into @spid, @uid, @blocked
while (@@fetch_status != -1)
begin
 print ''
 print ''
 exec ('print ''DBCC INPUTBUFFER FOR SPID ' + @spid + '''')
 exec ('dbcc inputbuffer (' + @spid + ')')
 print ''
 if (@blocked = '0')
 -- if DBCC PSS is not required comment the line above, remove the -- comment on the line below and run the stored procedure in fast mode
 -- if (@blocked = '0' and @fast = 0)
 begin
 exec ('print ''DBCC PSS FOR SPID ' + @spid + '''')
 exec ('dbcc pss (' + @uid + ', ' + @spid + ')')
 print ''
 print ''
 end
 fetch next from ibuffer into @spid, @uid, @blocked
end
deallocate ibuffer
if datediff(millisecond, @time, getdate()) > 1000
begin
 select @tmpchar='End time: ' + convert(varchar(26), @time, 113)
 print @tmpchar
end
 
dbcc traceoff(3604)
end -- All
go