Technical FAQs

Ask a Question

How to Terminate All Sessions Prior to Restoring a Database

Cannot restore a database.  Restore Database operation fails reporting that exclusive access could not be obtained because the database is in use.

Microsoft SQL Server

SQL Server 2005, SQL Server 2008 R2, SQL Server 2012, SQL Server 2014

Before an existing database can be restored, there should be no connections using the database. If the database is currently in use the RESTORE command fails with below error:
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.

To avoid this, we need to terminate all sessions which using the database. All sessions using the database can be obtained
by executing the system stored procedure sp_who2
or by querying the sys.dm_exec_sessions table as:
SELECT   session_id
FROM     sys.dm_exec_sessions
WHERE    DB_NAME(database_id) = 'RestoringDatabaseName'

You need to terminate each of the sessions returned individually by using KILL command.

If there are large number of sessions to terminate, or you need to do this on a routinely,this can be a lengthy and tedious process.
You can *automate* this using below script, which takes database name as input, and terminates all sessions connecting to it.

USE [master]
SET @sqlCmd = ''
SET @dbName = 'RestoringDatabaseName' -- Change database name here
SELECT   @sqlCmd = @sqlCmd + 'KILL ' + CAST(session_id AS VARCHAR) +
FROM     sys.dm_exec_sessions
WHERE    DB_NAME(database_id) = @dbName
PRINT @sqlCmd
--Uncomment below line to kill
--EXEC (@sqlCmd)

** see attachment containing the SQL script text
Was this helpful?
What can we do to improve the information ?