I want to close all the existing connections in MSSQL Server

Question

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

Manish Kumar · Answer

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.

Tapan Patel · Answer

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.

Manish Kumar · Answer

Closing from where ssms or visual studio?

Ranjeet Patra · Answer

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

I want to close all the existing connections in MSSQL Server

Answers (4)