Click on the banner to learn about and purchase my database training on Azure

How to drop all connections from a database in SQL Server

Views: 17.935 views
Reading Time: 3 minutes

Hello people,
Good evening.

Today I bring a quick tip for those who already wanted to do a quick restore, an alter database or any command that requires an exclusive lock on a database, but there were users running queries in the database, and then you get an error message like this SQL Server:

Msg 3101, Level 16, State 1, Line 2
Exclusive access could not be obtained because the database is in use.
Msg 3013, Level 16, State 1, Line 2
RESTORE DATABASE is terminating abnormally.

In this case, we must ensure that all existing connections using this database are disconnected and then try to execute our command again. The best way to do this is by contacting users and aligning their need for them to close their connections themselves and finish executing their queries.

It is never recommended to KILL connections without users' consent unless it is critical or a scheduled maintenance that was previously aligned.

Deleting database connections via KILL

If you really need to manually drop connections using a database, you can use this short T-SQL snippet to accomplish this task:

Deleting database connections via ALTER DATABASE

The above command is very practical and works without problems. However, it may occur between the time you drop sessions and run your RESTORE DATABASE, for example, other connections connect to the database. For this reason, the most recommended method for this operation is with ALTER DATABASE, putting the database in SINGLE_USER mode, where only a single user can connect at a time:

The WITH ROLLBACK IMMEDIATE parameter causes all sessions to close without warning and roll back immediately. Now you can perform your maintenance without any other connection interfering with your commands. At the end of your maintenance, remember to return the bank to MULTI_USER mode so that it can accept multiple connections again:

Deleting all connections from all databases

As a plus, I will post here two solutions to eliminate all sessions from all databases (except system ones).


Putting all instance databases in SINGLE_USER mode:

Returning all instance databases to MULTI_USER mode:
In this case, I could not use the sp_msforeachdb stored procedure as it does not list the databases in SINGLE_USER mode. In this case, I had to loop between databases manually.

That's it, dear readers.
To the next!