I want to close all the existing connections in MSSQL Server

Pravat Kumar

9y1.4k

I want to close all the existing connections in MS SQL Server

4 Replies

Know the answer? Post it — somebody with the same question will find it here.

Tapan Patel9y ago
If you are using the SSMS, then do a right click on one of the query windows and go to connection, click on Disconnect All Queries.

And if you want to get rid of all other processes like background processes or sleeping processes, then you may want to kill these processes after getting the process IDs from sys.sysprocesses table. Try out a dynamic query to kill these sessions.
0
Manish Kumar9y ago
If you are closing connection from ssms then try below query.

declare @tbl table (
spid varchar(100),ecid int,
status varchar(150),loginname varchar(120),hostname varchar(150),
blk int,dbname varchar(150),cmd varchar(150),requestid int)
insert into @tbl
exec sp_who
declare @spid varchar(20)
declare cur cursor for select 'kill '+spid from @tbl where loginname='EnterLoginDetail' and status<>'runnable' and dbname='YourDBName'
open cur
fetch next from cur into @spid
while @@FETCH_STATUS=0
begin
exec (@spid)
print @spid
fetch next from cur into @spid
end
close cur
deallocate cur

For checking puporse go to any open quey tab and run any select statement you will get below mesages.

A transport-level error has occurred when sending the request to the server. (provider: Shared Memory Provider, error: 0 - No process is on the other end of the pipe.)

It means it has been disconnected.

0
Manish Kumar9y ago
Closing from where ssms or visual studio?
0
Ranjeet Patra9y ago
Have a look at the below links:

http://stackoverflow.com/questions/10388041/how-to-close-existing-connections-to-a-db

http://stackoverflow.com/questions/30092513/how-to-close-all-existing-connections-to-a-db-programmatically

https://www.sqlservercentral.com/Forums/Topic1489912-2799-1.aspx
0