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.
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