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.