Kill Processes in sql server 2008
Hey folks till now we have discussed User defined functions in sql, View in sql , default constraint in sql , Remove Cross duplicate rows in SQL, Recursive SQL Query , Recursive SQL Query-2, STUFF and CONCAT in SQL, RANK in SQL server , Difference between temporary table and table variable in sql server, UNIQUEIDENTIFIER in SQL Server, RAW Mode with FOR XML , AUTO Mode with FOR XML, EXPLICIT Mode with FOR XML , PATH Mode with FOR XML, OUTPUT Clause in SQL Server, Difference between delete and truncate in sql server etc.
Today we will discuss regarding Kill Processes in sql server 2008. Many times we are in a position where we want to know all the running session or processes or query on particular database. Some query may last longer until we explicitly kill it. So lets go through the first step of above problem to get all running session or processes.
DECLARE @DBNAME VARCHAR(100) SET @DBNAME = 'MvcMovies' SELECT * FROM MASTER.DBO.SYSPROCESSES WHERE DB_NAME(DBID) = @DBNAME AND DBID <> 0 AND SPID <> @@SPID
The above query will return all the running session or processes on given database. Now to kill all this process use below query.
DECLARE @EXECSQL VARCHAR(1000) DECLARE @DBNAME VARCHAR(100) SET @DBNAME = 'MvcMovies' SET @EXECSQL = '' SELECT @EXECSQL = @EXECSQL + 'KILL ' + CONVERT(CHAR(10), SPID) + ' ' FROM MASTER.DBO.SYSPROCESSES WHERE DB_NAME(DBID) = @DBNAME AND DBID <> 0 AND SPID <> @@SPID PRINT @EXECSQL EXEC(@EXECSQL)
Now to cross check that all the processes had been killed, just run the first query again and see the output. It all goes right it will have no rows in the result set.