Blog Post

SQL Server – Kill all sessions using database


Before an existing database can be restored, there should be connections using the database in question. 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 kill all sessions using the database. All sessions using the database can be queries using system stored procedure sp_who2 or using sys.dm_exec_sessions DMV:

SELECT   session_id
FROM     sys.dm_exec_sessions
WHERE    DB_NAME(database_id) = 'SqlAndMe'

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

If there are large number of sessions to kill, or you need to do this on a routine basis it gets boring to do it this way. You can *automate* this using below script, which takes database name as input, and kills all sessions connecting to it.

-- Kill all sessions using a database
-- Vishal -
USE [master]
SET @sqlCmd = ''
SET @dbName = 'SqlAndMe' -- 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)

Hope This Helps!


If you like this post, do like my Facebook Page –> SqlAndMe

EMail me your questions ->

Follow me on Twitter -> @SqlAndMe

Filed under: Catalog Views, Management Views and Functions, SQLServer, SQLServer 2005, SQLServer 2008, SQLServer 2008 R2, SQLServer 2012


You rated this post out of 5. Change rating




You rated this post out of 5. Change rating