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


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


Comments (0)

Skip to main content