Here I will explain how to kill or close all active database connections in SQL Server.
In previous article I explained Get number of active connections to database in SQL Server, Joins in SQL Server and many articles relating to SQL Server. Now I will explain how to close or kill all database connections in SQL Server.
In some situations we will get requirement like close or kill active database connections for that we can approach in different methods
Open SQL Server Management Studio and connect to your database server
Now select database server >> Right click on server >> Select Activity Monitor like as shown below
Once we click on Activity Monitor all the details will open in right hand side like as shown below
Now click on Processes tab it will display all the session details in that select required session >> Right click on it and select Kill Process like as shown below
In this way we can kill required database connection in SQL Server.
If we use above method we need to close each connection of user separately instead of this we can use query to close all the database connections for that we need to write the query like as shown below
ALTER DATABASE YourDatabaseName SET SINGLE_USER WITH ROLLBACK IMMEDIATE
Whenever we run above query forcefully it will disconnect all the database connections and it will move our database mode to single user mode for that reason we need to move our database back to multi user mode. To convert single user to multi user we need to run the below query
ALTER DATABASE YourDatabaseName SET MULTI_USER
In this way we can kill or close all the database connections in SQL Server.