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.
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 - http://SqlAndMe.com USE [master] GO DECLARE @dbName SYSNAME DECLARE @sqlCmd VARCHAR(MAX) SET @sqlCmd = '' SET @dbName = 'SqlAndMe' -- Change database name here SELECT @sqlCmd = @sqlCmd + 'KILL ' + CAST(session_id AS VARCHAR) + CHAR(13) FROM sys.dm_exec_sessions WHERE DB_NAME(database_id) = @dbName PRINT @sqlCmd --Uncomment below line to kill --EXEC (@sqlCmd)
Hope This Helps!