0

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.

Muhammed Lutfu ODABASOGLU
  • 75
  • 7
  • What do you mean by "I won't be able to do some operations"? How are the existing connections preventing this? – Dan Guzman Jun 01 '20 at 11:38
  • Dear Sir, @DanGuzman There is another program runs in our server computer, that program' s source code doesn't belong to us and the programmers programmed that if there are any open connections to the server, we won't be able to update that program. In order to update the other program, all connections must be closed. So our program's connections also must be closed. It sometimes takes 30 mins to call every client and ask to quit our program. – Muhammed Lutfu ODABASOGLU Jun 01 '20 at 11:46

2 Answers2

1

Check out the table sys.dm_exec_sessions and the KILL statement

Wouter
  • 2,881
  • 2
  • 9
  • 22
0

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
Muhammed Lutfu ODABASOGLU
  • 75
  • 7