Killing all the active connections of an SQL Server database

Recently, when I was trying to restore a database, I got the below error and the database restoration failed. The error was because of some stray open connections to the database.

System.Data.SqlClient.SqlError: Exclusive access could not be obtained because the database is in use. (Microsoft.SqlServer.SmoExtended)

One way to overcome this issue is by killing all the active connections to the specific database. The simplest method to kill all the stray connections is by setting the database to a single user mode, perform the restore operation and then set it back to multi user mode.

Here is the statement to set the database to single user mode:

USE master;
GO
ALTER DATABASE Your_Database_Name
SET SINGLE_USER
WITH ROLLBACK IMMEDIATE;
GO

Now all the connections except the one used by you is killed. You can restore the database without any worry of someone or some app connecting the database. Once the restoration is complete, you can set the database back to multi user mode.

To set the database back to multi user mode:

ALTER DATABASE Your_Database_Name
SET MULTI_USER;
GO

Reference

• More about single-user mode at Microsoft Docs.