1

How to get active sessions details with database and object details in SQL Server?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Royals
  • 19
  • 1
  • 2
  • 1
    Possible duplicate of [How do I see active SQL Server connections?](https://stackoverflow.com/questions/1248423/how-do-i-see-active-sql-server-connections) – esqew Oct 11 '19 at 14:25

1 Answers1

3

The sys.dm_exec_sessions view reports all authenticated sessions within SQL Server. You can use the following query;

SELECT * FROM sys.dm_exec_sessions

For more detailed query you can use this;

SELECT s.session_id, s.login_time, s.host_name, s.program_name,
s.login_name, s.nt_user_name, s.is_user_process,
s.database_id, DB_NAME(s.database_id) AS [database], 
s.status,
s.reads, s.writes, s.logical_reads, s.row_count,
c.session_id, c.net_transport, c.protocol_type, 
c.client_net_address, c.client_tcp_port, 
c.num_writes AS DataPacketWrites 
FROM sys.dm_exec_sessions s
INNER JOIN sys.dm_exec_connections c
ON s.session_id = c.session_id 
INNER JOIN sys.dm_exec_requests r 
ON s.session_id = r.session_id
Esat Erkec
  • 1,575
  • 7
  • 13