Technical Article

Modified SP_WHOM

,

Just compile the 3 scripts in the order shown.  To execute the main script(sp_whom) use the following examples from SQL Server Management Studio.  Three result sets will be tabs will be displayed.  Drag the top one up to view results from all 3:

exec sp_whom

You can optionally pass in any or all of the 3 filters.

1) login name

2) host name

3) program name

This is designed for SQL Server 2005, 2008 and 2012 and works best with a GUI SQL tool loke SSMS or MS Visual Studio.

-- First compile this script for dependency #1

USE [master]
GO
CREATE PROCEDURE [dbo].[sp_connections]
    @loginame     sysname = NULL,
    @hostname     varchar(50) = NULL,
    @programname  varchar(150) = NULL
as

SELECT

  spid
 ,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_sysprocesscount
  from master.dbo.sysprocesses   (nolock)


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

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

         and     blocked       = 0

--------Allows filtering by login name - this used to work in sp_who
--------Modified by DK - 07/22/2008.  Epicor DBA.
IF (@loginame IS NOT NULL)
  BEGIN
    DELETE #tb1_sysprocesscount
    WHERE loginname <> @loginame
  END

IF (@hostname IS NOT NULL)
   BEGIN
    DELETE #tb1_sysprocesscount
    WHERE hostname <> @hostname
  END


select 
   loginname, 
   CASE hostname WHEN '' THEN @@servername ELSE hostname END HostName, 
   count(*) AS ConnectionCount 
from 
   #tb1_sysprocesscount 
group by
   loginname, 
--   hostname
   CASE hostname WHEN '' THEN @@servername ELSE hostname END
order by
   ConnectionCount desc

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

---------------------------------------------------------------
-- Second compile this script for dependency #2

CREATE PROCEDURE [dbo].[sp_blockinfo]
    @loginame     sysname = NULL,
    @hostname     varchar(50) = NULL,
    @programname  varchar(150) = NULL
as

declare 
   @blockcount int

SELECT

  spid
 ,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_sysblocks
  from master.dbo.sysprocesses   (nolock)
  where spid > 50


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

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

         and     blocked       = 0

--------Allows filtering by login name - this used to work in sp_who
IF (@loginame IS NOT NULL)
  BEGIN
    DELETE #tb1_sysblocks
    WHERE loginname <> @loginame
  END

IF (@hostname IS NOT NULL)
   BEGIN
    DELETE #tb1_sysblocks
    WHERE hostname <> @hostname
  END
  
IF (@programname IS NOT NULL)
   BEGIN  
    DELETE #tb1_sysblocks
    WHERE program_name <> @programname
  END     


select 
   @blockcount = count(*)
from 
   #tb1_sysblocks 
where 
   blocked <> ''

if @blockcount = 0
  begin
     select 'T h e r e   a r e   N O   B l o c k i n g   I s s u e s !' AS 'B l o c k i n g   I n f o'
   end
else
   begin
     select
  'Spid ' + CAST(spid AS VARCHAR) + ' is blocked by spid ' + CAST(blocked AS VARCHAR) as 'Blocking Issue'
 ,status
 ,hostname
 ,program_name
 ,cmd
 ,cpu
 ,physical_io
 ,dbid
 ,loginname
 ,spid_sort
 ,'kill ' + CAST(blocked AS VARCHAR) as 'Kill Command'
     from 
        #tb1_sysblocks 
     where 
        blocked <> ''
    end

if (object_id('tempdb..#tb1_sysblocks') is not null)
            drop table #tb1_sysblocks
GO
---------------------------------------------------------------------------
-- Main Script
-- SP_WHOM code

USE [master]
GO
CREATE PROCEDURE [dbo].[sp_whom]
    @loginame     sysname = NULL,
    @hostname     varchar(50) = NULL,
    @programname  varchar(150) = NULL
as

set nocount on

declare
    @retcode         int

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

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
 ,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) 
  where spid > 50


--------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

--------Allows filtering by login name - this used to work in sp_who
--------Modified by DK - 07/22/2008.  Epicor DBA.
IF (@loginame IS NOT NULL) AND (@loginame <> 'active')
  BEGIN
    DELETE #tb1_sysprocesses
    WHERE loginname <> @loginame
  END

IF (@hostname IS NOT NULL)
   BEGIN
    DELETE #tb1_sysprocesses
    WHERE hostname <> @hostname
  END
  
IF (@programname IS NOT NULL)
   BEGIN  
    DELETE #tb1_sysprocesses
    WHERE program_name <> @programname
  END   
--------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
      


--PRINT 'The session count is: ' + CAST(RTRIM(@sessionCount) AS VARCHAR)
--------Output the report.

EXECUTE(
'
SET nocount off

SELECT
             SPID          = convert(char(5),spid)

            ,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 + '

      -- (Seems always auto sorted.)   
      order by 
BlkBy desc,
spid_sort

SET nocount on
'
)
/*****AKUNDONE: removed from where-clause in above EXEC sqlstr
             sid >= ' + @charsidlow  + '
      and    sid <= ' + @charsidhigh + '
      and
**************/
IF (@loginame NOT IN ('active'))
BEGIN
EXEC sp_connections @loginame, @hostname, @programname
--EXEC sp_blocks @loginame, @hostname, @programname
EXEC sp_blockinfo @loginame, @hostname, @programname
END

LABEL_86RETURN:


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

return @retcode -- sp_whom
GO

Rate

★ ★ ★ ★ ★ ★ ★ ★ ★ ★

4 (9)

You rated this post out of 5. Change rating

Share

Join the discussion and add your comment

Share

Rate

★ ★ ★ ★ ★ ★ ★ ★ ★ ★

4 (9)

You rated this post out of 5. Change rating

Related content