Close Kill All Active Database Connections in SQL Server

Introduction

Here I will explain how to kill or close all active database connections in SQL Server.

Description

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

First Method

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.

Second Method: 

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.

If you enjoyed this post, please support the blog below. It's FREE! Get the latest Asp.net, C#.net, VB.NET, jQuery, Plugins & Code Snippets for FREE by subscribing to our Facebook, Twitter, RSS feed, or by email.

Subscribe by RSS Subscribe by Email