TopBlocker

------Set up schema to use.  I have a database called ADMINDB where this is stored.
-----------------------------------------------------------------------------


IF NOT EXISTS(SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'TopBlocker')
 CREATE TABLE dbo.TopBlocker(
 TopBlockerId int IDENTITY(1,1),
 TopSPID int NOT NULL,
 CommandText text NULL,
 Login varchar(500) NULL,
 HostName varchar(255) NULL,
 ProcessDate datetime CONSTRAINT [DF_TopBlocker_ProcessDate] DEFAULT getdate() NOT NULL
)
go

IF EXISTS (SELECT * FROM sysindexes WHERE id=OBJECT_ID('dbo.TopBlocker') AND name='IX_TopBlocker_ProcessDate_TopSPID')
 CREATE CLUSTERED INDEX IX_TopBlocker_ProcessDate_TopSPID ON dbo.TopBlocker(ProcessDate, TopSPID)
go

IF EXISTS (SELECT * FROM sysindexes WHERE id=OBJECT_ID('dbo.TopBlocker') AND name='PK_TopBlocker_TopBlockerId')
 ALTER TABLE dbo.TopBlocker ADD 
 CONSTRAINT PK_TopBlocker_TopBlockerId PRIMARY KEY NONCLUSTERED (TopBlockerId)
GO

-----------------------------------------------------------------------
-----------------------------------------------------------------------
----Run Stored procedure into database (ADMINDB in my case)
----Every time this procedure is executed, it logs the results into the 
----above table.  Set up a SQL Job to execute this as often as you desire
----I execute it every couple minutes.


IF EXISTS(SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_NAME = 'sp_who4')
 DROP PROCEDURE sp_who4
GO

CREATE PROCEDURE [dbo].[sp_who4](
 @loginame sysname = NULL,
 /* NEW PARAMETER ADDED BY CHB */ @hostname sysname = NULL) 
AS

set nocount on

if @hostname is null set @hostname = '0'

declare
 @retcode int

declare
 @sidlow varbinary(85)
 ,@sidhigh varbinary(85)
 ,@sid1 varbinary(85)
 ,@spidlow int
 ,@spidhigh int

declare
 @charMaxLenLoginName varchar(6)
 ,@charMaxLenDBName varchar(6)
 ,@charMaxLenCPUTime varchar(10)
 ,@charMaxLenDiskIO varchar(10)
 ,@charMaxLenHostName varchar(10)
 ,@charMaxLenProgramName varchar(10)
 ,@charMaxLenLastBatch varchar(10)
 ,@charMaxLenCommand varchar(10)

declare
 @charsidlow varchar(85)
 ,@charsidhigh varchar(85)
 ,@charspidlow varchar(11)
 ,@charspidhigh varchar(11)

--------

select
 @retcode = 0 -- 0=good ,1=bad.

--------defaults
select @sidlow = convert(varbinary(85), (replicate(char(0), 85)))
select @sidhigh = convert(varbinary(85), (replicate(char(1), 85)))

select
 @spidlow = 0
 ,@spidhigh = 32767

--------------------------------------------------------------
IF (@loginame IS NULL) --Simple default to all LoginNames.
 GOTO LABEL_17PARM1EDITED

--------

-- select @sid1 = suser_sid(@loginame)
select @sid1 = null
if exists(select * from master.dbo.syslogins where loginname = @loginame)
    select @sid1 = sid from master.dbo.syslogins where loginname = @loginame

IF (@sid1 IS NOT NULL) --Parm is a recognized login name.
 begin
 select @sidlow = suser_sid(@loginame)
 ,@sidhigh = suser_sid(@loginame)
 GOTO LABEL_17PARM1EDITED
 end

--------

IF (lower(@loginame) IN ('active')) --Special action, not sleeping.
 begin
 select @loginame = lower(@loginame)
 GOTO LABEL_17PARM1EDITED
 end

--------

IF (patindex ('%[^0-9]%' , isnull(@loginame,'z')) = 0) --Is a number.
 begin
 select
 @spidlow = convert(int, @loginame)
 ,@spidhigh = convert(int, @loginame)
 GOTO LABEL_17PARM1EDITED
 end

--------

RaisError(15007,-1,-1,@loginame)
select @retcode = 1
GOTO LABEL_86RETURN


LABEL_17PARM1EDITED:


-------------------- Capture consistent sysprocesses. -------------------

SELECT

 spid
,CAST(null AS VARCHAR(5000)) as commandtext
,status
,sid
,hostname
,program_name
,cmd
,cpu
,physical_io
,blocked
,dbid
,convert(sysname, rtrim(loginame))
 as loginname
,spid as 'spid_sort'

, substring( convert(varchar,last_batch,111) ,6 ,5 ) + ' '
 + substring( convert(varchar,last_batch,113) ,13 ,8 )
 as 'last_batch_char'

 INTO #tb1_sysprocesses
 from master.dbo.sysprocesses (nolock)

/*******************************************

FOLLOWING SECTION ADDED BY CHB 05/06/2004

RETURNS LAST COMMAND EXECUTED BY EACH SPID

********************************************/
CREATE TABLE #spid_cmds
(SQLID INT IDENTITY, spid INT, EventType VARCHAR(100), Parameters INT, Command VARCHAR(5000))

DECLARE spids CURSOR FOR
SELECT spid FROM #tb1_sysprocesses

DECLARE @spid INT, @sqlid INT

OPEN spids
FETCH NEXT FROM spids 
INTO @spid

/*
EXECUTE DBCC INPUTBUFFER FOR EACH SPID
*/
WHILE (@@FETCH_STATUS = 0)
BEGIN
    
    INSERT INTO #spid_cmds (EventType, Parameters, Command)
    EXEC('DBCC INPUTBUFFER( ' + @spid + ')')

    SELECT @sqlid = MAX(SQLID) FROM #spid_cmds

    UPDATE #spid_cmds SET spid = @spid WHERE SQLID = @sqlid     

    FETCH NEXT FROM spids INTO @spid

END

CLOSE spids
DEALLOCATE spids

UPDATE p
SET p.commandtext = s.command
FROM #tb1_sysprocesses P
JOIN #spid_cmds s
ON p.spid = s.spid

---------------------------------------------

--------Screen out any rows?

IF (@loginame IN ('active'))
 DELETE #tb1_sysprocesses
 where lower(status) = 'sleeping'
 and upper(cmd) IN (
 'AWAITING COMMAND'
 ,'MIRROR HANDLER'
 ,'LAZY WRITER'
 ,'CHECKPOINT SLEEP'
 ,'RA MANAGER'
 )

 and blocked = 0



--------Prepare to dynamically optimize column widths.


Select
 @charsidlow = convert(varchar(85),@sidlow)
 ,@charsidhigh = convert(varchar(85),@sidhigh)
 ,@charspidlow = convert(varchar,@spidlow)
 ,@charspidhigh = convert(varchar,@spidhigh)



SELECT
 @charMaxLenLoginName =
 convert( varchar
 ,isnull( max( datalength(loginname)) ,5)
 )

 ,@charMaxLenDBName =
 convert( varchar
 ,isnull( max( datalength( rtrim(convert(varchar(128),db_name(dbid))))) ,6)
 )

 ,@charMaxLenCPUTime =
 convert( varchar
 ,isnull( max( datalength( rtrim(convert(varchar(128),cpu)))) ,7)
 )

 ,@charMaxLenDiskIO =
 convert( varchar
 ,isnull( max( datalength( rtrim(convert(varchar(128),physical_io)))) ,6)
 )

 ,@charMaxLenCommand =
 convert( varchar
 ,isnull( max( datalength( rtrim(convert(varchar(128),cmd)))) ,7)
 )

 ,@charMaxLenHostName =
 convert( varchar
 ,isnull( max( datalength( rtrim(convert(varchar(128),hostname)))) ,8)
 )

 ,@charMaxLenProgramName =
 convert( varchar
 ,isnull( max( datalength( rtrim(convert(varchar(128),program_name)))) ,11)
 )

 ,@charMaxLenLastBatch =
 convert( varchar
 ,isnull( max( datalength( rtrim(convert(varchar(128),last_batch_char)))) ,9)
 )
 from
 #tb1_sysprocesses
 where
-- sid >= @sidlow
-- and sid <= @sidhigh
-- and
 spid >= @spidlow
 and spid <= @spidhigh



--------Output the report.

create table #who (SPID int, CommandText TEXT, 
    Status varchar(50), Login varchar(75), HostName varchar(50), BlkBy varchar(10),
    DBName varchar(100), Command varchar(50), CPUTime int, DiskIO int, LastBatch varchar(100), ProgramName varchar(100),
    SPID2 int)

EXECUTE(
'
SET nocount off

Insert #who 
SELECT
 SPID = convert(char(5),spid)
     ,CommandText    

 ,Status =
 CASE lower(status)
 When ''sleeping'' Then lower(status)
 Else upper(status)
 END

 ,Login = substring(loginname,1,' + @charMaxLenLoginName + ')

 ,HostName =
 CASE hostname
 When Null Then '' .''
 When '' '' Then '' .''
 Else substring(hostname,1,' + @charMaxLenHostName + ')
 END

 ,BlkBy =
 CASE isnull(convert(char(5),blocked),''0'')
 When ''0'' Then '' .''
 Else isnull(convert(char(5),blocked),''0'')
 END

 ,DBName = substring(case when dbid = 0 then null when dbid <> 0 then db_name(dbid) end,1,' + @charMaxLenDBName + ')
 ,Command = substring(cmd,1,' + @charMaxLenCommand + ')

 ,CPUTime = substring(convert(varchar,cpu),1,' + @charMaxLenCPUTime + ')
 ,DiskIO = substring(convert(varchar,physical_io),1,' + @charMaxLenDiskIO + ')

 ,LastBatch = substring(last_batch_char,1,' + @charMaxLenLastBatch + ')

 ,ProgramName = substring(program_name,1,' + @charMaxLenProgramName + ')
 ,SPID = convert(char(5),spid) --Handy extra for right-scrolling users.
 from
 #tb1_sysprocesses --Usually DB qualification is needed in exec().
 where
 spid >= ' + @charspidlow + '
 and spid <= ' + @charspidhigh + '
 and (HostName like ''' + @hostname + '%'' or ''' + @hostname + ''' = ''0'')

 -- (Seems always auto sorted.) order by spid_sort

SET nocount on
'
)

--IF EXISTS(select * from #who WHERE CONVERT(VARCHAR(25), SPID) <> BlkBy AND BlkBy > '.')
--BEGIN
--    SELECT BlkBy, count(*) FROM #who GROUP BY BlkBy
--    SELECT * FROM #who
--END

--SELECT w.SPID, w.BlkBy, blkg.CommandText 
--from #who Blkg
--    LEFT OUTER JOIN #who w ON CONVERT(VARCHAR(25), w.SPID) = Blkg.BlkBy 
--WHERE w.SPID IS NULL

CREATE TABLE #TopBlocker(
SPID INT,
BlkBy VARCHAR(10),
Login varchar(75), 
HostName varchar(50),
CommandText text
)
DECLARE @now DATETIME SET @now = GETDATE()

INSERT INTO #TopBlocker(SPID, BlkBy, CommandText, Login, HostName)
select w.SPID, w.BlkBy, blkg.CommandText, blkg.LOGIN, blkg.HostName
from #who w
    LEFT OUTER JOIN #who Blkg ON CONVERT(VARCHAR(25), Blkg.SPID) = w.BlkBy 
WHERE CONVERT(VARCHAR(25), w.SPID) <> w.BlkBy 
AND w.BlkBy > '.'
--ORDER BY w.SPID

INSERT INTO TopBlocker(TopSPID, CommandText, Login, HostName, ProcessDate)
SELECT TopSPID = tb.BlkBy, tb.CommandText, tb.LOGIN, tb.HostName, @now--, count(*)
from #TopBlocker tb 
    LEFT OUTER JOIN #TopBlocker tb2 ON tb.BlkBy = CONVERT(VARCHAR(25), tb2.SPID)
WHERE tb2.SPID IS NULL
--GROUP BY tb.BlkBy, tb.CommandText

SELECT tb.TopBlockerId, tb.TopSPID, tb.CommandText, tb.Login, tb.HostName, tb.ProcessDate
FROM TopBlocker tb
WHERE ProcessDate = @now

LABEL_86RETURN:


if (object_id('tempdb..#tb1_sysprocesses') is not null)
 drop table #tb1_sysprocesses

DROP TABLE #spid_cmds
DROP TABLE #who

GO
--------

--Query I run for results
SELECT tb.TopBlockerId,
 tb.TopSPID,
 CONVERT(VARCHAR(8000), tb.CommandText),
 tb.Login,
 tb.HostName,
 tb.ProcessDate
FROM TopBlocker tb
WHERE TopSPID > 10
ORDER BY ProcessDate DESC

GO