--**************************************
--     
-- Name: sp_CatchProcesses
-- Description:I wrote this for a bottle
--     necked SQL box working with some badly w
--     ritten 3rd party code that my company wa
--     s unable to change. It quickly gives a b
--     reakdown of all waiting/blocked processe
--     s and what they are waiting for
-- By: Keith Henry
--
-- Returns:Table containing blocked/wait
--     ing info. Most from sysprocesses/self-ex
--     planatory except: HoldingDescription - D
--     escription of whatever is blocking, Wait
--     ResourceString - Description of waitreso
--     urce code, [SQL Statement] - the first 2
--     55 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 a
--     nywhere else remove this prefix.
--
--This code is copyrighted and has-- limited warranties.Please see http://
--     www.Planet-Source-Code.com/vb/scripts/Sh
--     owCode.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