How do I see active SQL Server connections?

I am using SQL Server 2008 Enterprise. I want to see any active SQL Server connections, and the related information of all the connections, like from which IP address, connect to which database or something.

Are there existing commands to solve this issue?

8 Answers

You can use the sp_who 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.

SELECT DB_NAME(dbid) as DBName, COUNT(dbid) as NumberOfConnections, loginame as LoginName FROM sys.sysprocesses WHERE dbid > 0 GROUP BY dbid, loginame ; 

See also the Microsoft documentation for sys.sysprocesses.

Apart from sp_who, you can also use the "undocumented" sp_who2 system stored procedure which gives you more detailed information. See Difference between sp_who and sp_who2.

Click the "activity monitor" icon in the toolbar.

From Thorsten's comment:

In SQL Server Management Studio, right click on Server, choose "Activity Monitor" from context menu -or- use keyboard shortcut Ctrl + Alt + A.

Reference: Microsoft Docs - Open Activity Monitor in SQL Server Management Studio (SSMS)

Below is my script to find all the sessions connected to a database and you can check if those sessions are doing any I/O and there is an option to kill them.

The script shows also the status of each session.

Have a look below.

--============================================================================== -- See who is connected to the database. -- Analyse what each spid is doing, reads and writes. -- If safe you can copy and paste the killcommand - last column. -- Marcelo Miorelli -- 18-july-2017 - London (UK) -- Tested on SQL Server 2016. --============================================================================== USE master go SELECT sdes.session_id ,sdes.login_time ,sdes.last_request_start_time ,sdes.last_request_end_time ,sdes.is_user_process ,sdes.host_name ,sdes.program_name ,sdes.login_name ,sdes.status ,sdec.num_reads ,sdec.num_writes ,sdec.last_read ,sdec.last_write ,sdes.reads ,sdes.logical_reads ,sdes.writes ,sdest.DatabaseName ,sdest.ObjName ,sdes.client_interface_name ,sdes.nt_domain ,sdes.nt_user_name ,sdec.client_net_address ,sdec.local_net_address ,sdest.Query ,KillCommand = 'Kill '+ CAST(sdes.session_id AS VARCHAR) FROM sys.dm_exec_sessions AS sdes INNER JOIN sys.dm_exec_connections AS sdec ON sdec.session_id = sdes.session_id CROSS APPLY ( SELECT DB_NAME(dbid) AS DatabaseName ,OBJECT_NAME(objectid) AS ObjName ,COALESCE(( SELECT TEXT AS [processing-instruction(definition)] FROM sys.dm_exec_sql_text(sdec.most_recent_sql_handle) FOR XML PATH('') ,TYPE ), '') AS Query FROM sys.dm_exec_sql_text(sdec.most_recent_sql_handle) ) sdest WHERE sdes.session_id <> @@SPID AND sdest.DatabaseName ='yourdatabasename' --ORDER BY sdes.last_request_start_time DESC --============================================================================== 

UPDATE 20th Jan 2022

I have now a better version that uses sys.dm_tran_locks This is particularly useful when you need all users off an specific database, and that's why I have this parameter @dbname

Even if someone just opens SSMS and connect to a database it will show up in this query.

DECLARE @dbname SYSNAME =NULL SELECT sdes.session_id ,sdes.login_time ,sdes.last_request_start_time ,sdes.last_request_end_time ,sdes.is_user_process ,sdes.host_name ,sdes.program_name ,sdes.login_name ,sdes.status ,sdec.num_reads ,sdec.num_writes ,sdec.last_read ,sdec.last_write ,sdes.reads ,sdes.logical_reads ,sdes.writes ,DatabaseName = COALESCE( db_name(sdes.database_id), N'') ,sdest.ObjName ,sdes.client_interface_name ,sdes.nt_domain ,sdes.nt_user_name ,sdec.client_net_address ,sdec.local_net_address ,sdest.Query ,KillCommand = 'Kill '+ CAST(sdes.session_id AS VARCHAR) from sys.dm_tran_locks t INNER JOIN sys.dm_exec_sessions sdes ON T.request_session_id = sdes.session_id LEFT OUTER JOIN sys.dm_exec_connections AS sdec ON sdec.session_id = sdes.session_id OUTER APPLY ( SELECT DB_NAME(dbid) AS DatabaseName ,OBJECT_NAME(objectid) AS ObjName ,COALESCE(( SELECT TEXT AS [processing-instruction(definition)] FROM sys.dm_exec_sql_text(sdec.most_recent_sql_handle) FOR XML PATH('') ,TYPE ), '') AS Query FROM sys.dm_exec_sql_text(sdec.most_recent_sql_handle) ) sdest where t.resource_type = 'database' and t.resource_database_id = CASE WHEN @dbname IS NULL THEN t.resource_database_id ELSE DB_ID(@dbname) END and t.request_type = 'LOCK' and t.request_status = 'GRANT' 

I threw this together so that you could do some querying on the results

Declare @dbName varchar(150) set @dbName = '[YOURDATABASENAME]' --Total machine connections --SELECT COUNT(dbid) as TotalConnections FROM sys.sysprocesses WHERE dbid > 0 --Available connections DECLARE @SPWHO1 TABLE (DBName VARCHAR(1000) NULL, NoOfAvailableConnections VARCHAR(1000) NULL, LoginName VARCHAR(1000) NULL) INSERT INTO @SPWHO1 SELECT db_name(dbid), count(dbid), loginame FROM sys.sysprocesses WHERE dbid > 0 GROUP BY dbid, loginame SELECT * FROM @SPWHO1 WHERE DBName = @dbName --Running connections DECLARE @SPWHO2 TABLE (SPID VARCHAR(1000), [Status] VARCHAR(1000) NULL, [Login] VARCHAR(1000) NULL, HostName VARCHAR(1000) NULL, BlkBy VARCHAR(1000) NULL, DBName VARCHAR(1000) NULL, Command VARCHAR(1000) NULL, CPUTime VARCHAR(1000) NULL, DiskIO VARCHAR(1000) NULL, LastBatch VARCHAR(1000) NULL, ProgramName VARCHAR(1000) NULL, SPID2 VARCHAR(1000) NULL, Request VARCHAR(1000) NULL) INSERT INTO @SPWHO2 EXEC sp_who2 'Active' SELECT * FROM @SPWHO2 WHERE DBName = @dbName 

MS's query explaining the use of the KILL command is quite useful providing connection's information:

SELECT conn.session_id, host_name, program_name, nt_domain, login_name, connect_time, last_request_end_time FROM sys.dm_exec_sessions AS sess JOIN sys.dm_exec_connections AS conn ON sess.session_id = conn.session_id; 

You can perform the following T-SQL command:

SELECT * FROM sys.dm_exec_sessions WHERE status = 'running';