sp_CatchProcesses

				
--**************************************
-- Name: sp_CatchProcesses
-- Description:I wrote this for a bottlenecked SQL box working with some badly written 3rd party code that my company was unable to change. It quickly gives a breakdown of all waiting/blocked processes and what they are waiting for
-- By: Keith Henry
--
-- Returns:Table containing blocked/waiting info. Most from sysprocesses/self-explanatory except: HoldingDescription - Description of whatever is blocking, WaitResourceString - Description of waitresource code, [SQL Statement] - the first 255 chars of the statement the process is trying to run.
--
-- Assumes:This has a sp_ prefix for use in the master database. If you use it anywhere else remove this prefix.
--
--This code is copyrighted and has-- limited warranties.Please see http://www.Planet-Source-Code.com/vb/scripts/ShowCode.asp?txtCodeId=520&lngWId=5--for details.--**************************************

create proc sp_CatchProcesses
as
	begin
	set nocount on 
	
	create table #tempInputBuff (EventType varchar(255),Parameters int,EventInfo varchar(255))
	declare	@TempSysprocesses TABLE (
		HoldingDescription varchar(255) null,
		spid smallint NULL ,
		kpid smallint NULL ,
		blocked smallint NULL ,
		waittype binary (2) NULL ,
		waittime int NULL ,
		lastwaittype nchar (32) NULL ,
		waitresource nchar (256) NULL ,
		dbid smallint NULL ,
		uid smallint NULL ,
		status nchar (30) NULL ,
		sid binary (86) NULL ,
		hostname nchar (128) NULL ,
		program_name nchar (128) NULL ,
		hostprocess nchar (8) NULL ,
		cmd nchar (16) NULL ,
		SQLStmt varchar(255) null
	)
	
	declare @tempSpid int
	
	begin tran
	delete
	from @TempSysprocesses 
	insert @TempSysprocesses (HoldingDescription,hostname,program_name,cmd,spid,kpid,waittime,lastwaittype,waitresource,status,dbid)
	select	(
			(	case
					when s.waittime > 0 then (
						'Waiting for ' + 
						(	case rtrim(s.waitresource) 
								when '' then rtrim(s.LastWaitType)
								else	isnull(rtrim(s.waitresource),'unknown') + 
									', Held by' + isnull (
									(	select 	top 1
											' Host: ' + isnull(rtrim(msw.hostname),'') + 
											', Prog: ' + isnull(rtrim(msw.program_name),'') +
											(case when msw.waittime > 0 then ', Wait for: ' + (case rtrim(msw.waitresource) when '' then rtrim(msw.LastWaitType) else isnull(rtrim(msw.waitresource),'Non Found') end) else '' end) +
											(case when msw.blocked > 0 then ', Block by: ' + cast(msw.blocked as varchar) else '' end)
										from	master..sysprocesses as msw with (nolock)
										where	cast(msw.spid as varchar) = s.waitresource
									),' ' + rtrim(s.LastWaitType))
							end
						)
					) + ' ' 
					else ''
				end
			) +
			(	case
					when s.blocked <> 0 then
						'blocked by ID ' + 
						cast(s.blocked as varchar) + 
						', blocked by detail (' + isnull (
						(	select 	top 1
								' Host: ' + isnull(rtrim(msb.hostname),'') + 
								', Prog: ' + isnull(rtrim(msb.program_name),'') +
								(case when msb.waittime > 0 then ', Wait for: ' + (case rtrim(msb.waitresource) when '' then rtrim(msb.LastWaitType) else isnull(rtrim(msb.waitresource),'Non Found') end) else '' end) +
								(case when msb.blocked > 0 then ', Block by: ' + cast(msb.blocked as varchar) else '' end)
							from	master..sysprocesses as msb with (nolock)
							where	msb.spid = s.blocked
						),'') + ') '
					else ''
				end
			) + 
			(	case
					when s.spid in (	select	distinct msb2.blocked
									from	master..sysprocesses as msb2 with (nolock)	) then
						'Blocking'
					else ''
				end
			)
		) as HoldingDescription, s.hostname,s.program_name,s.cmd,s.spid,s.kpid,s.waittime,s.lastwaittype,s.waitresource,s.status,s.dbid
	from	master..sysprocesses as s with (nolock)
	where	(	s.spid in (	select	distinct ms.blocked
					from	master..sysprocesses as ms with (nolock)	) 
		)
		or
		s.blocked <> 0 
		or s.waittime > 0
	if @@rowcount > 0 
	begin
		declare tc cursor for (select spid from @TempSysprocesses where kpid <> 0)
		OPEN tc
			FETCH NEXT FROM tc INTO @tempSpid
			WHILE (@@FETCH_STATUS = 0)
			BEGIN
				delete from #tempInputBuff
				insert #tempInputBuff EXEC ('DBCC INPUTBUFFER (' + @tempSpid + ') WITH NO_INFOMSGS')
				
				update 	@TempSysprocesses
				set	SQLStmt = (select top 1 EventInfo from #tempInputBuff)
				where	spid = @tempSpid
				FETCH NEXT FROM tc INTO @tempSpid
			END
		CLOSE tc
		DEALLOCATE tc
		print 'Locked/Wait at ' + convert(char(8),getdate(),108)
		select	convert(char(8),getdate(),108) as [Time],s.spid,s.kpid,s.cmd,s.HoldingDescription,
			s.hostname, s.program_name,s.waittime,s.lastwaittype,
			cast(rtrim(s.waitresource) as char(32)) as waitresource, s.status, 
			(	select	top 1 d.[name] 
				from	master..sysdatabases d 
				where	d.dbid = s.dbid
			) as [Database],
			(select	'Waiting for ' + (
					case left(s.waitresource,3)
						when 'TAB' then 'Table'
						when 'PAG' then 'Page'
						when 'KEY' then 'Index'
						when 'RID' then 'Row'
						else 'resource'
					end) + ' in ' + db.[Name]
			from	master..sysdatabases as db with (nolock)
			where	db.dbid = (	case 
							when s.waitresource like '%:%' then
								case 	
									when left(s.waitresource,charindex(':',s.waitresource)-1) like '[0-9]%' then left(s.waitresource,charindex(':',s.waitresource)-1)
									else ltrim(substring(s.waitresource,charindex(':',s.waitresource)+1,abs(charindex(':',s.waitresource,charindex(':',s.waitresource)+1) - (charindex(':',s.waitresource)+1))))
								end
							else 0
						end)
			) as WaitResourceString, replace(replace(replace(SQLStmt, char(10) + char(13), ' '),char(10),' '),char(13),' ') as [SQL Statement]
		from 	@TempSysprocesses s
		order by s.spid
	end
 	ELSE
 		print 'No Locks or waits at ' + convert(varchar,getdate(),108)
	commit tran
	drop table #tempInputBuff
end
GO