how to find all the sleeping sessions which are created for DQL commands in SQL Server

Question

Actually, I would like to kill some sessions in SQL Server which are sleeping for so long. But, I got a doubt that if we kill any sleeping session which is having DML commmands, it may rollback the transaction and probably cause some other issues. So, I want to kill only those sessions which are created for select queries. Is there any way that I can find those sessions which are having only DQL commands.

Answer 1

Sleeping user sessions without open transactions can be identified by querying sys.dm_exec_sessions:

SELECT * 
FROM sys.dm_exec_sessions 
WHERE status = N'sleeping' AND open_transaction_count = 0 AND is_user_process = 1;

These sessions do not consume significant resources but, as noted in your comment, the application is not properly closing connections and there are limitations on the client app side with the number of connections.

Be aware that the application will get a SQL exception if an attempt is made to use killed sessions and the impact will depend on the exception handing. You may want to also check last_request_start_time to avoid killing sleeping sessions that may be used again for another query. Of course, the app code should be remediated as the long term fix.