More than running sp_who2, which gives you more information than the good ol’ sp_who, or querying sys.dm_exec_sessions to check all sessions running in your SQL Server, you need be able to flag to suspicious or questionable sessions thats running transactions in your server. You need to identify which session has multiple connections or sessions with context switching or sessions with orphaned transactions then move on to eliminate these sessions.
Logins with more than one session
To identify logins running multiple sessions in your SQL Server you may run query below
SELECT login_name, COUNT(session_id) AS session_count
WHERE is_user_process = 1
GROUP BY login_name
ORDER BY login_name
If you have a baseline figure of number of connections per login, including logins you may have at the application level, then you know if a login has a doubtful number of connections. Though this may not at all present a critical problem, it’s a nice-to-have information at hand that you can discuss with the development team regarding their application design.
Sessions with context switching
Context switching is the executing a T-SQL statement under the guise of another user connection in order to use their access rights that comes with their credential. To identify if there is such session running in your SQL Server, you may run below query and still using sys.dm_exec_sessions DMV;
SELECT session_id, login_name, original_login_name
WHERE login_name <> original_login_name AND is_user_process = 1
Sessions with orphaned transactions
The query below will filter out all sessions with orphaned transactions, still using the same DMV, dm_exec_sessions.
FROM sys.dm_exec_sessions des
INNER JOIN sys.dm_tran_session_transactions dtst ON des.session_id = dtst.session_id
LEFT JOIN sys.dm_exec_request der ON dtst.session_id = der.session_id
WHERE der.session_id IS NULL
ORDER BY des.session_id
The LEFT JOIN on sys.dm_exec_request and returning only for NULL results in sessions that are still open and yet do not have a corresponding request associated to that session.
Sessions that are inactive
To filter out all open sessions with still active connections but have been inactive or have no active request running in your SQL Server, you can run the query below.
FROM sys.dm_exec_sessions des INNER JOIN sys_dm_tran_session_transactions dtst ON des.session_id = dtst.session_id
WHERE des.is_user_process = 1 AND DATEDIFF(dd, des.Last_request_end_time, GETDATE()) > 3
ORDER BY des.last_request_end_time
From the result of the above query, you can watch out for the [STATUS] column that is not equal to ‘Running’ (maybe ‘Sleeping’, ‘Dormant’, etc). In m sample query above, i filter out all open sessions for the last 3 days but you can change this to any number of day as your case maybe.
Now that you have identified sessions that are orphaned or inactive sessions, you may proceed to clean up your sessions and KILL those who are not needed anymore.