Get all the current users, sessions, and processes in an instance of the Microsoft SQL Server Database Engine
Views (3719)
Tharanga Chandrasekara
23,118
Like
Share
ReportHave 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.
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' Thank you and Regards,
Tharanga Chandrasekara
This was originally posted here.
*This post is locked for comments