Dec 25, 2015

Get all the current users, sessions, and processes in an instance of the Microsoft SQL Server Database Engine

Have seen many of the forum users are posting question on how to find active user sessions in an instance of Microsoft SQL Database Engine. 


In order to get the current users, sessions, and processes in an instance of the Microsoft SQL Server Database Engine, we can use the "sp_who2" SQL stored procedure. 


Following is the definition provided for "sp_who2" in the TechNet
Provides information about current users, sessions, and processes in an instance of the Microsoft SQL Server Database Engine. The information can be filtered to return only those processes that are not idle, that belong to a specific user, or that belong to a specific session.
"sp_who2" return a data set as a output and in order to capture the data set we can use a temporary table. Following is the sample code that would return all the current users, sessions, and processes in an instance of the Microsoft SQL Server Database Engine.


 DECLARE @Table TABLE(  
     SPID INT,  
     Status VARCHAR(MAX),  
     LOGIN VARCHAR(MAX),  
     HostName VARCHAR(MAX),  
     BlkBy VARCHAR(MAX),  
     DBName VARCHAR(MAX),  
     Command VARCHAR(MAX),  
     CPUTime INT,  
     DiskIO INT,  
     LastBatch VARCHAR(MAX),  
     ProgramName VARCHAR(MAX),  
     SPID_1 INT,  
     REQUESTID INT  
 )  
 INSERT INTO @Table EXEC sp_who2  
 SELECT *  
 FROM  @Table  
 WHERE DBName = 'DatabaseName'  

Please provide your feedback with a comment.
Thank you and Regards,
Tharanga Chandrasekara