dump tran sybsystemprocs with truncate_only
go

use sybsystemprocs
go

if exists (select * from sysobjects where name = "sp_blocker" and type = 'P')
   drop proc sp_blocker
go

create proc sp_blocker
as

-- This procedures identifies all spid's that are 
-- blocking other spid's, but are not themselves blocked.
-- Author: Christopher Eastman, Virtualogic
-- 301/571-5100 x146
-- Written: 3/21/97

select 	distinct "blocker" = p.spid, 
	"login" = convert(varchar(12), suser_name(p.suid)), 
	"database" = convert(varchar(12),  db_name(p.dbid)),
	"table" = convert(varchar(20), object_name(l.id, l.dbid)),
	cmd,
	program_name,
	hostname,
	hostprocess
from master..syslocks l, master..sysprocesses p
where p.spid = l.spid
and p.blocked=0
and exists 
		(select * from master..sysprocesses p2
		where p2.blocked = p.spid
		-- Always 0, so ignore for now
		-- and time_blocked > 30 
		)

if @@rowcount > 0
begin
	print ""
	print "Some blocking is normal.  If blocking persists, record the"
	print "information above and use the 'kill SPID' command to kill the"
	print "blocking process.  Use the SPID from the blocker column."
end
else
	print "No blocking detected at this time."
go

if object_id('sp_blocker') is not null
begin
    print '<<< Created procedure dbo.sp_blocker >>>'
    grant execute on dbo.sp_blocker to public
end
else
begin
    print '<<< Failed creating proc dbo.sp_blocker >>>'
end
go