Whenever we try to take an exclusive access to a database in order to perform any activity like backup restore, it usually fails if any process is still using the database. I received the given below error from my test database while I was restoring the database on it.
Opsssss, fortunately, we do have solution by killing the running process to take a lock and perform any activity using a management command namely KILL & a system view sys.sysprocesses. However, what encourages me to write this solution is that sys.sysprocesses will be removed from the future version of SQL Server. So what is the NEW solution ?
Given below is the script, that I developed using sys.dm_exec_sessions.
USE master
GO
DECLARE @SQL_String VARCHAR(MAX)
DECLARE @Database_id INT
--DO NOT forget to change database name in give below statement
SET @Database_id=DB_ID('AdventureWorks2012')
SET @SQL_String=''
SELECT @SQL_String=@SQL_String + 'KILL '
+ COALESCE(CONVERT(VARCHAR(5),[session_id])+ '; ','')
FROM sys.dm_exec_sessions
WHERE database_id =@Database_id
--PRINT @SQL_String
EXEC (@SQL_String)
The above statement kills all the process running on a specific database. But do not forget to CHANGE the database name.
Once the above statement executed successfully, you can perform any activity.
SQL Server Portal 