Archive for the ‘Management Command’ Category

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.

kill process.1.1

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
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.

Read Full Post »