Show commands running SQL server

Category: SQL SQL Server

If you want to see which commands are running on SQL Server, run the query below:

SET NOCOUNT ON    

DECLARE
    @SPID	    SMALLINT
   ,@SQLID          SMALLINT
   ,@SqlStatement   VARCHAR(4000) 

IF object_id('tempdb..#tbl_sysprocesses') IS NOT NULL
	DROP TABLE #tbl_sysprocesses     

IF object_id('tempdb..#spid_cmds') IS NOT NULL
	DROP TABLE #spid_cmds     

IF object_id('tempdb..#spid') IS NOT NULL
	DROP TABLE #spid

SELECT
  spid
 ,CAST(NULL AS VARCHAR(8000)) AS commandtext
 ,status
 ,sid
 ,hostname
 ,program_name
 ,cmd
 ,cpu
 ,physical_io
 ,blocked
 ,dbid
 ,convert(SYSNAME, rtrim(loginame)) AS loginname
 ,substring(convert(VARCHAR,last_batch,111) ,6  ,5 )
		+ ' ' +  substring( convert(VARCHAR,last_batch,113) ,13 ,8 )
       as 'last_batch_char'
 INTO   #tbl_sysprocesses
 FROM   master.dbo.sysprocesses (NOLOCK)
 WHERE  NOT(lower(status)  = 'sleeping'
         	AND upper(cmd) IN (
             'AWAITING COMMAND'
            ,'MIRROR HANDLER'
            ,'LAZY WRITER'
            ,'CHECKPOINT SLEEP'
            ,'RA MANAGER' )
         	AND blocked = 0)

--Show only active processes
DELETE #tbl_sysprocesses
WHERE lower(status)  = 'sleeping'
   AND upper(cmd) IN  (
        'AWAITING COMMAND'
       ,'LAZY WRITER'
      ,'CHECKPOINT SLEEP' )     AND blocked = 0  

CREATE TABLE #spid_cmds
(
     SQLID       INT IDENTITY
    ,SPID        INT
    ,EventType   VARCHAR(30)
    ,Parameters  INT
    ,Command     VARCHAR(8000)
)       

SELECT DISTINCT spid INTO #spid FROM #tbl_sysprocesses    

WHILE (1 = 1)
BEGIN
   SET @SPID = NULL
   SELECT TOP 1 @SPID = spid FROM #spid 	     

   IF (@SPID IS NULL) GOTO _NEXT

   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      

   DELETE FROM #spid WHERE spid = @SPID
END    

_NEXT:

UPDATE p
SET p.commandtext = s.command
FROM #tbl_sysprocesses p
INNER JOIN #spid_cmds s ON p.spid = s.spid    

SET @SqlStatement =
'
   SELECT     SPID          = spid
     	    ,CommandText
             ,Status        =
                  rtrim(CASE lower(status)
                     When ''sleeping'' THEN lower(status)
                     Else                   upper(status)
                  END)
            ,Login         = loginname
            ,HostName      =
                  CASE hostname
                     When NULL  THEN ''  .''
                     When '' '' THEN ''  .''
                     Else    hostname
                  END
            ,BlkBy         =
                  CASE ISNULL(blocked,0)
                     When 0 THEN 0
                     Else blocked
                  END
            ,DBName        = case when dbid = 0 THEN NULL
			    when dbid <> 0 THEN db_name(dbid) END
            ,Command       = cmd
            ,CPUTime       = cpu
            ,DiskIO        = physical_io
            ,LastBatch     = last_batch_char
            ,ProgramName   = program_name
      FROM  #tbl_sysprocesses
      ORDER BY CPUTime DESC
'
EXEC(@SqlStatement)

DROP TABLE #tbl_sysprocesses
DROP TABLE #spid_cmds
DROP TABLE #spid  

SET NOCOUNT ON