sp_who2 - filtering and sorting the results

Category : Tips User Rating : 4.5 Stars      Views : 5 Stars
One Click Feedback
Please help us to improve the site by rating the quality of this article by clicking a button below.




The stored procedure sp_who2 lists all current processes connected to a SQL Server :
exec sp_who2

A typical resultset from Management Studio is :

sp_who2 results

sp_who2 is one of the most useful and widely used stored procedures, along with its predecessor sp_who. However it is also one of the most frustrating as it only takes a single parameter and the results cannot be ordered. For a large server with a lot of connections this can be a real nuisance. I usually store the results in a temporary table and then filter and/or order the results from there :

CREATE TABLE #sp_who2 (SPID INT,Status VARCHAR(255),
      Login  VARCHAR(255),HostName  VARCHAR(255),
      BlkBy  VARCHAR(255),DBName  VARCHAR(255),
      Command VARCHAR(255),CPUTime INT,
      DiskIO INT,LastBatch VARCHAR(255),
      ProgramName VARCHAR(255),SPID2 INT,
      REQUESTID INT)
INSERT INTO #sp_who2 EXEC sp_who2
SELECT      *
FROM        #sp_who2
-- Add any filtering of the results here :
WHERE       DBName <> 'master'
-- Add any sorting of the results here :
ORDER BY    DBName ASC
 
DROP TABLE #sp_who2
Some people encapsulate the above code in a stored procedure and run that, but my preference is always to run it as a script.

Finally

A word of warning. Sp_who2 is undocumented, meaning that Microsoft could change it in the future without warning. I’ve tested the code above on SQL Server 2005, 2008 and 2008 R2, however it’s possible that the columns or datatypes returned could change in future versions which would require a small change in the code.

Related Articles

The following articles may also be of interest :

Link back to this article : http://www.sqlmatters.com/Articles/sp_who2 - filtering and sorting the results.aspx

Keywords

TSQL,sp_who,sp_who2,who


Comments
Post by TheDeb on Thu 24 Jan 2013 18:35. Report Inappropriate Post

Perfect! Just what I was looking for. Thank you!
Post by Jake on Wed 30 Jan 2013 14:42. Report Inappropriate Post

Thanks for the simply query, exactly what I needed.
Post by chris on Mon 04 Feb 2013 14:30. Report Inappropriate Post

V. Useful thanks ;)

Works on SS2012
Post by Henk on Tue 01 Oct 2013 09:19. Report Inappropriate Post

Works like a charm & very usefull - thank you!
Post by bernardo on Thu 10 Oct 2013 16:59. Report Inappropriate Post

muy bueno, gracias
Post by Geraldo on Wed 23 Oct 2013 20:01. Report Inappropriate Post

Valeu!!!! Me ajudou.. Thank you!!!
Post by Faith on Wed 15 Jan 2014 14:01. Report Inappropriate Post

how to pull data result from sp_who2 to asp.net web app?
Post by turboys on Tue 18 Feb 2014 11:07. Report Inappropriate Post

Can sys.sp_who2 modify ?when I right click it says Modify / Refresh - therefore I selected Modify and Execute- Received an error Invalid object name 'sys.sp_who2'.

anyone can help?

Post by Peter Keats on Tue 04 Mar 2014 16:05. Report Inappropriate Post

Nice. Thanks for making it look so easy. :)
Post by Jeff on Fri 18 Apr 2014 16:45. Report Inappropriate Post

Its easier to use a table variable so you don't have to deal with temp tables:

declare @tempTable table (SPID INT,Status VARCHAR(255),
Login VARCHAR(255),HostName VARCHAR(255),
BlkBy VARCHAR(255),DBName VARCHAR(255),
Command VARCHAR(255),CPUTime INT,
DiskIO INT,LastBatch VARCHAR(255),
ProgramName VARCHAR(255),SPID2 INT,
REQUESTID INT);

INSERT INTO @tempTable
EXEC sp_who2

select *
from @tempTable
Post by John on Fri 18 Jul 2014 16:52. Report Inappropriate Post

This is awesome! Exactly what I was looking for. Many thanks!!!!!
Post by ARSH on Sat 20 Dec 2014 10:02. Report Inappropriate Post

THANKS NICE INFO


Post by Dan MacNeil on Wed 07 Jan 2015 19:32. Report Inappropriate Post
Website : http://howto.omacneil.org/
Thank you for putting this out there for Dr Google to share with me. My life is easier now. I will help a older person across the street with my time savings.

Also nice to have general principle of inserting stored procedure output into a temp table.
Post by Damien Cornwall on Wed 01 Apr 2015 15:11. Report Inappropriate Post

AWESOME!! You just shaved years off my life
Post by Foley on Mon 06 Apr 2015 19:16. Report Inappropriate Post

AWESOME JOB!!!
Post by Oscar Luna on Wed 15 Apr 2015 22:24. Report Inappropriate Post

trank you,
Post by Hudson Santos on Fri 02 Oct 2015 16:59. Report Inappropriate Post
Website : http://www.smallbee.com.br
Great workaround! Easy to filter when you get lots of databases.
Post by Fabio on Mon 28 Dec 2015 19:32. Report Inappropriate Post

Great article!
Post by Joe Hayes on Mon 28 Dec 2015 21:37. Report Inappropriate Post

Thanks! I've used this several times, and it's very useful :)
Post by Siva on Wed 09 Mar 2016 20:19. Report Inappropriate Post

Excellent Solution
Post by Karthikeyan A on Tue 14 Jun 2016 10:23. Report Inappropriate Post

select 'dbcc inputbuffer(' + convert(char,(spid)) + ')' , * from sys.sysprocesses

You can try this system table for all info of Sp_who2
Post by DiegoSierra on Mon 25 Jul 2016 20:03. Report Inappropriate Post

Excellent information. Solution for different needs and/or preferences
Post by DiegoSierra on Mon 25 Jul 2016 20:53. Report Inappropriate Post

Here is my own version of sp_who (sp_who3) combining info from the posts above:

declare @tempTable table
( SPID SMALLINT
, Status NCHAR(30)
, Login NCHAR(128)
, HostName NCHAR(128)
, BlkBy SMALLINT
, DBName VARCHAR(255)
, Command NCHAR(16)
, CPUTime INT
, Disk_IO BIGINT
, LastBatch DATETIME
, ProgramName NCHAR(128)
, REQUESTID INT
, HostProcess NCHAR(10)
, LoginTime DATETIME
, OpenTransactions SMALLINT
)

INSERT INTO @tempTable
SELECT SPID
, sysprocesses.STATUS
, LOGINAME
, HOSTNAME
, BLOCKED
, sysdatabases.name
, CMD
, CPU
, PHYSICAL_IO
, LAST_BATCH
, PROGRAM_NAME
, REQUEST_ID
, HOSTPROCESS
, LOGIN_TIME
, OPEN_TRAN
FROM sys.sysprocesses
JOIN master.dbo.sysdatabases
ON sysprocesses.DBID = sysdatabases.DBID

SELECT *
FROM @tempTable
-- Add any filtering of the results here :
WHERE DBName <> 'master'
-- Add any sorting of the results here :
ORDER BY SPID ASC

Post by Oscar Luna on Fri 19 Aug 2016 16:08. Report Inappropriate Post

Hello! Here is my own version of sp_who combined with INPUTBUFFER:

BEGIN TRANSACTION --Cambie LastBatch por ActiveTime y ProgramName por CurrentSQL
CREATE TABLE #sp_who3 (SPID VARCHAR(255), Status VARCHAR(255), Login VARCHAR(255), HostName VARCHAR(255), BlockedBy VARCHAR(255), DBName VARCHAR(255), Command VARCHAR(255), CPUTime INT, DiskIO INT, ActiveTime VARCHAR(1024), CurrentSQL VARCHAR(4444), SPID2 INT, REQUESTID INT)
INSERT INTO #sp_who3 EXEC sp_who2 active

UPDATE #sp_who3 SET ActiveTime = CONVERT(VARCHAR(8), GETDATE() - SUBSTRING(ActiveTime,7,14), 108), CurrentSQL = '-'

---------------------------------------------------------------------------------------------------------------------
DECLARE @SpidActual VARCHAR(255)
DECLARE cursorwho3 CURSOR FOR (SELECT DISTINCT SPID FROM #sp_who3 WHERE (ActiveTime > '00:00:07') AND (Login <> 'sa') )

OPEN cursorwho3
FETCH NEXT FROM cursorwho3 INTO @SpidActual

WHILE @@FETCH_STATUS = 0
BEGIN
CREATE TABLE #inputbufferTable (myEventType nvarchar(256), myParameters int, myEventInfo nvarchar(4000) )
INSERT INTO #inputbufferTable EXEC(' BEGIN TRY
DBCC INPUTBUFFER(' + @SpidActual + ')
END TRY
BEGIN CATCH
END CATCH ' )
UPDATE #sp_who3 SET CurrentSQL = (SELECT myEventInfo FROM #inputbufferTable) WHERE SPID = @SpidActual
DROP TABLE #inputbufferTable
FETCH NEXT FROM cursorwho3 INTO @SpidActual
END

CLOSE cursorwho3
DEALLOCATE cursorwho3

---------------------------------------------------------------------------------------------------------------------
SELECT ActiveTime, SPID, Login, BlockedBy, CurrentSQL, Status, Command, DBName, CPUTime, DiskIO, HostName
FROM #sp_who3
WHERE (Login <> 'sa') AND (CurrentSQL IS NOT NULL)
ORDER BY ActiveTime DESC

DROP TABLE #sp_who3
ROLLBACK TRANSACTION
Post by Thami on Tue 17 Jan 2017 08:27. Report Inappropriate Post

Thank you very much. Worked for me to pinpoint an issue that has been persisting for days.
Post by Vijay on Sun 12 Feb 2017 21:46. Report Inappropriate Post

What is SPID ?

Post a comment   No login required !

 
Name :   Email :   Website :  
Will be displayed alongside your comment
Not displayed Optional, but displayed if entered