use admin
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[activeSPIDs]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[ActiveSPIDs]
GO

create proc dbo.ActiveSPIDs
as
/*******************************************************************************************************
*	admin.dbo.ActiveSPIDs
*	Creator:	Bill Wunder 
*
*       Date:           3-13-2003 
*
*	Project:	utility
*
*	Description:	show current runnable, blocking and, if exists, blocked spids
*
*	Usage:
	     EXECUTE dbo.ActiveSPIDs
*       notes: build on all servers and run as CTL-F1 (Tool|Customize|Custom tab) in Query Analyzer.  
*
*	Modifications:   
*         Developer Name     Date     Brief Description
*         ------------------ -------- ------------------------------------------------------------
*          bw                7-4-2003 show current activity not cumulative and make multiuser
*          bw                09-02-03 add fn_get_sql for blocker            
********************************************************************************************************/
declare @blocker int
	, @spid int
	, @handle binary(20)
	, @start int
	, @end int

set nocount on
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED 

-- use a permanent table so multiple users can watch at the same time
if object_id('dbo.RunningSPIDs','U') is null
	begin
		create table dbo.RunningSPIDs
			(spid smallint
				, host varchar(15)
				, cpu int
				, io bigint
				, lastcpu int
				, lastio bigint
				, start varchar(20)
				, app varchar(35)
				, recdt datetime
				, lastrecdt datetime
				, op varchar(20)
				, open_tran smallint)
	end

delete r
from dbo.RunningSPIDs r
left join master.dbo.sysprocesses p
on p.spid = r.spid
where cast(p.last_batch as varchar(20)) <> r.start
or p.spid is null
or p.status <> 'runnable' 

update r
	set cpu = p.cpu
		, io = cast(p.physical_io as int)
		, lastcpu = r.cpu
		, lastio = r.io
		, recdt = getdate()
		, lastrecdt = r.recdt
		, open_tran = p.open_tran
from master.dbo.sysprocesses p
left join msdb.dbo.sysjobs j
on substring(p.program_name,charindex('0x', p.program_name) + 18, 16)
   = substring(replace(j.job_id, '-',''),17,16) 
join dbo.RunningSPIDs r 
on p.spid = r.spid
where (p.status='runnable' 
	or p.spid in (Select blocked from master.dbo.sysprocesses where blocked <> 0))
and p.spid<>@@spid
and r.app = case when p.program_name like 'SQLAgent - TSQL JobStep%'
		then 'Job: ' + substring(j.name,1,30)
		else substring(p.program_name,1,35)
		end 

insert dbo.RunningSPIDs 
select p.spid
	, substring(p.hostname,1,15)
	, p.cpu
	, cast(p.physical_io as int)
	, null
	, null
	, cast(p.last_batch as varchar(20))
	, case when p.program_name like 'SQLAgent - TSQL JobStep%'
		then 'Job: ' + substring(j.name,1,30)
		else substring(p.program_name,1,35)
		end
	, getdate()
	, null
	, substring(replace(replace(replace(p.cmd,char(13),char(32)),char(10),char(32)),char(9), char(32)),1,20)
	, p.open_tran
from master.dbo.sysprocesses p
left join msdb.dbo.sysjobs j
on substring(p.program_name,charindex('0x', p.program_name) + 18, 16)
   = substring(replace(j.job_id, '-',''),17,16) 
where (p.status='runnable' 
	or p.spid in (Select blocked from master.dbo.sysprocesses where blocked <> 0))
and p.spid<>@@spid
and not exists (select 1 from dbo.RunningSPIDs where spid = p.spid)


print '"Runnable" SPIDs ordered by CPU usage' 
print '		[cur cpu] and [cur io] in last [cur dur] (current duration) seconds - rerun proc to refresh'
print '		Null [cur dur] means [cur cpu] and [cur io] values are total values since batch started.'
print '		If [cur dur] and batch started not changing check for open tran -->'
print ''

select spid
	, isnull(cpu-lastcpu,cpu) [cur cpu]
	, isnull(io-lastio,io) [cur io]
	, datediff(second,lastrecdt,recdt) as [cur dur]
	, start as [batch started]
	, host
	, op
	, app 
	, open_tran
	, cpu [tot cpu]
	, io [tot io]
from dbo.RunningSPIDs
order by cpu desc


if exists(select 1 from master.dbo.sysprocesses where blocked <> 0)
	begin
		print 'Blocking and Blocked SPIDs'
		print ''
		select p.spid,
			p.blocked [Blocker],
			p.waittime,
			cast(p.lastwaittype as varchar(20)) [lastwaittype],
			cast(rtrim(ltrim(p.waitresource)) as varchar(20)) [waitresource],
			cast(p.last_batch as varchar(20)) as [last batch],
			substring(p.hostname,1,15) as [Host Name],
			substring(replace(replace(replace(p.cmd,char(13),char(32)),char(10),char(32)),char(9), char(32)),1,20) as [op],
			case when p.program_name like 'SQLAgent - TSQL JobStep%'
				then 'Job: ' + substring(j.name,1,20)
				else substring(p.program_name,1,25)
				end as [Application Name]
			from master.dbo.sysprocesses p
			left join msdb.dbo.sysjobs j
			on substring(p.program_name,charindex('0x', p.program_name) + 18, 16)
			   = substring(replace(j.job_id, '-',''),17,16) 
			where p.spid<>@@spid
			and (p.blocked <> 0
				or p.spid in (select blocked 
						from master.dbo.sysprocesses
						where blocked <> 0))
			order by blocked, p.last_batch
                 
                select @blocker = min(blocked) 
		from master.dbo.sysprocesses
		where blocked > 0
                
                while @blocker is not null
                        begin
				select @handle = sql_handle 
					, @start = stmt_start
					, @end = stmt_end
				from master.dbo.sysprocesses
				where spid = @blocker

				 print 'Info on Blocking SPID ' + cast(@blocker as varchar(10))
				IF NOT EXISTS (SELECT * FROM ::fn_get_sql(@Handle))  
					select 'Unknown, handle not found in cache' [Currently Executing Statement]

	                        else  
				select replace(replace(replace(substring(text, 
						(@start + 2)/2,CASE @end
								WHEN -1 THEN (datalength(text))
								ELSE (@end - @start + 2)/2
								END)
							,char(13),char(32))
						,char(10),char(32))
					,char(9),char(32)) [Currently Executing Statement]
				from ::fn_get_sql(@handle)
	
                                print 'Input Buffer:'
                                dbcc inputbuffer(@blocker) 

                                print 'Output Buffer:'
                                dbcc outputbuffer(@blocker) 

                                print 'Output Buffer:'
				exec sp_lock @blocker

                                select @blocker = min(blocked) 
		                from master.dbo.sysprocesses 
		                where blocked > @blocker
                        end
 	end

RETURN

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO