use master
go
if OBJECT_ID("sp_blocked") IS NOT NULL
	drop procedure sp_blocked
go
create procedure sp_blocked
as

/*
** If you have a blocking problem (a process in the middle of a transaction
** that has resources locked and other processes are in a live lock, or deadlock 
** waiting for these resources to become available)
** This is a quick way to display what processes are causing the block(s) and 
** what processes are being affected by them.  
**
** SQL 4.2, 6.0, 6.5
**
** Given birth by 
** Charles Lucking
** Support Engineer
** IKON Office Solutions
** Tucson, Az 
** ChuckLucking@IKON.NET
*/

set nocount on
if exists ( select * from master..sysprocesses where blocked <> 0 )

begin
	/* show top blockers, but no duplicates */

	select convert(char(24),getdate(),13)
	select 'Blocker(s) causing the problem.'
	select distinct
	'ID'       = str( spid, 2 ),
	'Status'   = convert( char(10), status ),
	'Blk'      = str( blocked, 2 ),
	'Station'  = convert( char(10), hostname ),
	'User'     = convert( char(10), suser_name( suid ) ),
	'DbName'   = convert( char(10), db_name( dbid ) ),       
	'Program'  = convert( char(10), program_name ),
	'Command'  = convert( char(16), cmd ),
	'    CPU'  = str( cpu, 7 ),
	'     IO'  = str( physical_io, 7 )
	from master..sysprocesses
	where spid in ( select blocked from master..sysprocesses )
	and blocked = 0
	order by str(spid,2)

	/* show victims */

	select 'Victims of the above blocker(s).'
	select 'ID'= str( spid, 2 ),
	'Status'   = convert( char(10), status ),
	'Blk'      = str( blocked, 2 ),
	'Station'  = convert( char(10), hostname ),
	'User'     = convert( char(10), suser_name( suid ) ),
	'DbName'   = convert( char(10), db_name( dbid ) ),
	'Program'  = convert( char(10), program_name ),
	'Command'  = convert( char(16), cmd ),
	'    CPU'  = str( cpu, 7 ),
	'     IO'  = str( physical_io, 7 )
	from master..sysprocesses
	where blocked <> 0
	order by spid
end

else
begin
	select "There are NO blocks at this time.", convert (char(24),getdate(),13)
end

return
go