How to list opened sql server connections and close the specific one

Question

We have a remote SQL server and c# ERP client programs that use it. Sometimes people are opening the program and forget to close it. In that situation, their SQL connections are staying opened and I won't be able to do some operations.

Yes, I can do this from the client side by setting a timer and keep checking if they do any operations or they are AFK but, I want the power to close the connections without the client side's volition.

To sum up, 1- Is there any way to check the open all connections to the server and list them by a SQL query or command. 2- Force to close the specific connection I want even they are sleeping or not?

Any help would be appreciated. Thank you so much.

Answer 1

For Listing:

select *
from sys.dm_exec_sessions

For Killing specific connection by id:

declare @session_id     int;
set          @session_id= ''

select 
    @session_id=cast(req.session_id as int) from sys.dm_exec_requests req where req.command='DbccSpaceReclaim'group by req.session_id

begin

declare @sql nvarchar(1000)
select @sql = 'kill ' +  cast(@session_id  as varchar(50))
 exec sp_executesql  @sql
end

Answer 2

Check out the table sys.dm_exec_sessions and the KILL statement