如何查看活动的SQL Server连接?

我使用SQL Server 2008企业。我想看到任何活动的SQL Server连接，以及所有连接的相关信息，比如从哪个IP地址，连接到哪个数据库或其他东西。

是否存在解决此问题的现有命令?

1339757 次浏览
小开
最佳答案

可以使用sp_who存储过程。

在Microsoft SQL Server数据库引擎实例中提供有关当前用户、会话和进程的信息。可以对信息进行筛选，以只返回那些不空闲的进程、属于特定用户的进程或属于特定会话的进程。

小开

除了sp_who，你还可以使用“无文档的”sp_who2系统存储过程，它会提供更详细的信息。看到 sp_who和sp_who2的差异。

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

另请参阅微软文档中的sys.sysprocesses。

小开

点击“活动监视器”;工具栏中的图标。

From Thorsten's comment:

在SQL Server Management Studio中，右键单击服务器，选择“Activity monitor”;从上下文菜单-或使用键盘快捷键Ctrl + Alt + 一个。

参考:Microsoft Docs -在SQL Server Management Studio (SSMS)中打开活动监视器

小开

我把这些放在一起，这样你就可以对结果进行一些查询

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
小开

下面是我找到所有连接到数据库的会话的脚本，你可以检查这些会话是否正在做任何I/O，并且有一个选项可以杀死它们。

该脚本还显示每个会话的状态。

看看下面的内容。

--==============================================================================
-- 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


--==============================================================================

2022年1月20日更新

我现在有一个更好的版本，使用sys.dm_tran_locks 当你需要所有用户离开特定的数据库时，这是特别有用的，这就是为什么我有这个参数@dbname

即使有人只是打开SSMS并连接到数据库，它也会显示在这个查询中。

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'
小开

MS的查询解释了KILL命令的使用，提供了连接的信息，非常有用:

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;
小开

执行的T-SQL命令如下:

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

提交答案