T-SQL: Investigating session activity
July 19, 2011 Leave a comment
Shows what is currently running on an instance including SQL text.
SELECT DB_NAME(req.database_id) as [Database], sqltext.TEXT, req.session_id, s.login_time, req.status, req.command, req.cpu_time, req.total_elapsed_time, s.login_name, s.last_request_start_time, req.reads, req.writes FROM sys.dm_exec_requests req CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS sqltext left join sys.dm_exec_sessions s on s.session_id = req.session_id inner join sys.databases d on d.database_id = req.database_id where req.session_id > 50
Shows details of all connected sessions on an instance, can be narrowed down by uncommenting login_name
SELECT session_id, login_time, host_name, program_name, cpu_time, client_interface_name, login_name, [status], last_request_start_time, last_request_end_time FROM sys.dm_exec_sessions s --where login_name = 'sa' ORDER BY login_time DESC