如何查看活动的 SQL Server 连接？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1248423/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How do I see active SQL Server connections?
提问by George2
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.
我正在使用 SQL Server 2008 企业版。我想查看任何活动的 SQL Server 连接，以及所有连接的相关信息，例如来自哪个 IP 地址、连接到哪个数据库等等。
Are there existing commands to solve this issue?
是否有解决此问题的现有命令？
回答by Mehrdad Afshari
You can use the sp_whostored procedure.
您可以使用sp_who存储过程。
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.
提供有关 Microsoft SQL Server 数据库引擎实例中当前用户、会话和进程的信息。可以过滤信息以仅返回那些不空闲、属于特定用户或属于特定会话的进程。
回答by Syed Umar Ahmed
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.
另请参阅sys.sysprocesses的 Microsoft 文档。
回答by Sklivvz
Apart from sp_who, you can also use the "undocumented" sp_who2system stored procedure which gives you more detailed information. See Difference between sp_who and sp_who2.
除此之外sp_who，您还可以使用“未记录的”sp_who2系统存储过程，它为您提供更详细的信息。请参阅sp_who 和 sp_who2 之间的区别。
回答by Fernando Santos
Click the "activity monitor" icon in the toolbar...
单击工具栏中的“活动监视器”图标...
From Thorsten's comments:
来自 Thorsten 的评论：
In SQL Server Management Studio, right click on Server, choose "Activity Monitor" from context menu -or- use keyboard shortcut Ctrl+ Alt+ A.
在 SQL Server Management Studio 中，右键单击服务器，从上下文菜单中选择“活动监视器” - 或 - 使用键盘快捷键Ctrl+ Alt+ A。
回答by Marcello Miorelli
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.
下面是我的脚本，用于查找连接到数据库的所有会话，您可以检查这些会话是否正在执行任何 I/O，并且有一个选项可以杀死它们。
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
--==============================================================================
回答by Don Rolling
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
回答by Zalakain
MS's query explaining the use of the "kill" command is quite useful providing connection's information:
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;
HTH, regards,
HTH，问候，
回答by sinkmanu
You can perform the following T-SQL command:
您可以执行以下 T-SQL 命令：
SELECT * FROM sys.dm_exec_sessions WHERE status = 'running';
