This site is hosted and sponsored by hyve.com specialists in Cloud Hosting UK and VMware Hosting. If you are interested in our services please call us for chat on 0800 612 2524

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

Back to SQL Server