Saturday, January 1, 2011

Active SQL Server connections


These are just a few helpful DMV requests, returning details regarding the active SQL Server connections.  Keep in mind, user sessions are >= session_id 51.


Report all the connections to SQL Server, returning one row for each:
  SELECT
    connection_id,
    session_id,
    client_net_address,
    auth_scheme
 FROM
    sys.dm_exec_connections


Report each session connected to SQL Server, similar to sp_who2:
 SELECT
    session_id,login_name,
    last_request_end_time,cpu_time
 FROM
    sys.dm_exec_sessions
 WHERE
    session_id >= 51
 ORDER BY
    last_request_end_time DESC


Report details for what each connection is actually doing:
  SELECT
    session_id,
    status,
    command,
    sql_handle,
    database_id
 FROM
    sys.dm_exec_requests
 WHERE
    session_id >= 51

No comments:

Post a Comment