//*Display connection and blocking info
Author Nigel Rivett 
web http://www.nigelrivett.net/SQLAdmin/sp_nrInfo.html
Do you often need to find out what is being processed on your server?
How long each process has been running, what the command was, what is blocking, how much i/o it is causing?

This stored procedure will display the last command executed and other attributes for all spids or those with a given status.
The default status is 'runnable' as that is the usually requirement.
By calling the procedure with 'all' all spid are displayed.
By calling the proceduer with a spid just that spid is displayed.
By calling the proceduer with a 'blk' all blocked and blocking spids are displayed.

To keep a record or what is happenning on your server output the result of this procedure to a table and schedule the call.

Note: Sometimes the status displayed is not that requested due to the delay between getting the list of spids and the delay.


sp_nrinfo2 will give an output similar to sp_who2 active but also giving blocking spids and their command.
*/
if exists (select * from sysobjects where id = object_id(N'[dbo].[sp_nrinfo]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sp_nrinfo]
GO

Create procedure sp_nrInfo
@status varchar(20) = 'active' ,
@type   varchar(10) = 'info'
as
/*
exec sp_nrSpidByStatus			-- all spids whith status runnable
exec sp_nrSpidByStatus 'sleeping'	-- all spids whith status sleeping
exec sp_nrSpidByStatus 'background'	-- all spids whith status background
exec sp_nrSpidByStatus 'sleeping'	-- all spids whith status sleeping
exec sp_nrSpidByStatus 'all'		-- all spids
exec sp_nrSpidByStatus 'blk'		-- all blocked or blocking spids
exec sp_nrSpidByStatus '74'		-- an individual spid - also gives subthreads
select * from master..sysprocesses where spid = 56
*/
set transaction isolation level read uncommitted
set nocount on
-- Get blocking
declare @blkspid varchar(20)

select top 1 @blkspid = convert(varchar(20),spid) from master..sysprocesses 
where spid in (select blocked from master..sysprocesses)
order by blocked

if @blkspid is not null
begin
	-- this is all that will appear on the intranet
	select 	[blocking spid] = spid ,
		last_batch = last_batch ,
		hostname = left(hostname,14) ,
		program_name = left(program_name,20) ,
		cmd = cmd ,
		physical_io = physical_io
	from master..sysprocesses
	where spid = @blkspid
	
	exec ('dbcc inputbuffer (' + @blkspid + ')')
	
	print  'blocking spid command'
	DECLARE @sql_handle binary(20)
	SELECT	@sql_handle = sql_handle
			FROM master.dbo.sysprocesses
			WHERE	spid = @blkspid
				AND ecid = 0
  			SELECT *
   			FROM ::fn_get_sql(@sql_handle)
	
	print 'sp_who2 result for blocking spid'
	exec ('sp_who2 ' + @blkspid) 
	
	print 'spids that are being blocked'
	select spid, blocked from master..sysprocesses 
	where spid in (select blocked from master..sysprocesses)
	order by blocked
end

declare	@cmd varchar(1000)

declare @buf varchar(1000) ,
	@id int ,
	@spid int ,
	@maxSpid int
	create table #spid (spid int, command varchar(1000) null)
	create table #temp (x varchar(100), y int, s varchar(1000), id int identity (1,1))
	create table #spids (spid int)
	if isnumeric(@status) = 1
	begin
		insert #spids select @status
	end
	else if @status = 'blk'
	begin
		insert	#spids 
		select spid from master..sysprocesses where blocked <> 0
		union
		select blocked from master..sysprocesses where blocked <> 0
	end
	else if @status = 'active'
		insert	#spids 
		select 	distinct spid 
		from 	master..sysprocesses 
		where 	blocked <> 0
		or	upper(cmd)    not in 	(
				                     'AWAITING COMMAND'
				                    ,'MIRROR HANDLER'
				                    ,'LAZY WRITER'
				                    ,'CHECKPOINT SLEEP'
				                    ,'RA MANAGER'
						)
		or lower(status) <> 'sleeping'
	else
	begin
		insert	#spids select spid from master..sysprocesses where (status = @status or @status = 'all') and ecid = 0
	end
	select 	@spid = 0 ,
		@maxSpid = max(spid)
	from	#spids
	
	if @type = 'info'
	begin
		while @spid < @maxSpid
		begin
			select	@spid = min(spid) from #spids where spid > @spid
			
			select @cmd = 'dbcc inputbuffer (' + convert(varchar(10),@spid) + ')'
			
			delete #temp
			
			insert #temp
			exec (@cmd)
			
			select 	@id = 0 ,
				@buf = ''
			select @buf = @buf + replace(replace(s,char(10),'|'),char(13),'|')
	
			from #temp
	
			insert 	#spid
			select	@spid, @buf
		end
	end
	else
	begin
		insert	#spid select spid, '' from #spids
	end
	
	select 	spid 		= convert(varchar(4),#spid.spid) ,
		status 		= min(left(case when s.status = 'Runnable' then 'aRunnable' else 'z' + s.status end ,12)) ,
		threads		= count(*) ,
		loginame 	= min(left(s.loginame, 25)) ,
		hostname	= min(left (s.hostname, 14)) ,
		BlkBy		= max(case when s.blocked <> 0 then convert(varchar(3),s.blocked) else '   ' end) ,
		DBName		= left(db_name(min(dbid)), 10) ,
		command		= min(left(s.cmd, 30)) ,
		CPUTime		= sum(s.cpu) ,
		DiskIO		= sum(s.physical_io) ,
		LastBatch	= max(convert(varchar(23),s.last_batch,121)) ,
		ProgramName	= min(left(s.program_name, 30)) ,
		spid		= convert(varchar(4),#spid.spid) ,
		buffercmd	= max(#spid.command)
	from	#spid ,
		master..sysprocesses s
	where	s.spid = #spid.spid
	group by #spid.spid
	order by convert(int,#spid.spid) desc

	drop table #spid
	drop table #temp

go
grant execute on sp_nrInfo to public
go

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

Create procedure sp_nrinfo2
as
	exec sp_nrInfo 'active', 'noinfo'
go
grant execute on sp_nrinfo2 to public
go