Share via

SYSK 67: SQL – Who is Connected and What are They Running?

  • Article

Want to know who the users that are connected and how many sessions do each of them have?  Then use the new sys.dm_exec_sessions view:
      SELECT login_name, count(session_id) as session_count FROM sys.dm_exec_sessions GROUP BY login_name

Want to know who is running what at that exact instance?  Then run the statement below:
      SELECT st.text as [Command text], login_time, [host_name],
program_name, sys.dm_exec_requests.session_id, client_net_address,
sys.dm_exec_requests.status, command, db_name(database_id) as DatabaseName
FROM
sys.dm_exec_requests
INNER JOIN sys.dm_exec_connections on sys.dm_exec_requests.session_id = sys.dm_exec_connections.session_id
INNER JOIN sys.dm_exec_sessions on sys.dm_exec_sessions.session_id = sys.dm_exec_requests.session_id
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS st
WHERE
sys.dm_exec_requests.session_id >= 51
GO

Source:  http://msdn2.microsoft.com/en-us/library/ms176013.aspx and  http://www.replicationanswers.com/Script21.asp