Closing all connections to the database

Have you tried to restore the development database when suddenly such an error occurred?

Exclusive access could not be obtained because the database is in use

In the MS SQL Server with a connection to ColdFusion, this is a common problem. ColdFusion is not closing connections to speed up future queries, but it also causes such issues when trying to restore the development (or – in fact – any) database.

Checking active connections

To check what are the active connections, I’m using sys.dm_exec_sessions (for SQL Server 2012 and above) or master.sysprocesses (for older databases). 

-- OLD SQL SERVER
SELECT spid FROM master..sysprocesses 
where dbid = db_id('my-database-name')

-- SQL SERVER 2012 and above
SELECT session_id FROM sys.dm_exec_sessions
WHERE database_id = db_id('my-database-name')

If there are in fact some connections to close, I can simply kill such connections by providing the session id (spid) in this command:

kill [spid];

-- example:
kill 45;

Automate the task

Sometimes there is no one, but many connections. In such a case it is faster to use the query which will build the query string and execute all kill commands at once:

-- OLD SQL SERVER

USE master;

DECLARE @kill_query varchar(max); SET @kill = '';  
SELECT @kill_query = @kill_query + 'kill ' + CONVERT(varchar(10), spid) + '; '  
FROM master..sysprocesses  
WHERE dbid = db_id('my-database-name')

EXEC(@kill_query); 

-- SQL SERVER 2012 and above

USE [master];

DECLARE @kill_query varchar(max) = '';  
SELECT @kill_query = @kill_query + 'kill ' + CONVERT(varchar(10), session_id) + '; '  
FROM sys.dm_exec_sessions
WHERE database_id = db_id('my-database-name')

EXEC(@kill_query);

Here is the example displaying contents of the ‘kill query’:

In the example above I changed ‘exec’ to ‘select’ in order to display the contents of the query built by the script.