How to kill / terminate all running processes on Sql Server 2008

Question

After executing this query on master db, it gives me the whole process of working in all databases, is there any request that will kill the whole process running in the database.

USE Master GO SELECT SPID,DBID FROM SYSPROCESSES WHERE DBID NOT IN (1,2,3,4) AND SPID >50 AND SPID<> @@spid 

Answer 1

If you want to force disconnect any other connection, and you have the appropriate permissions, you can drop the database into and out of single-user mode :

 alter database current set single_user with rollback immediate; go alter database current set multi_user; go 

Any other connection to the same database will be completed.

Answer 2

You can use the KILL operator in conjunction with the cursor on the query result above.

See Kill (Transact-SQL)

Answer 3

Below you can see the SQL script to kill all processes for a given database (SQL Server 2014)

 Declare @DbName nvarchar(60)='YourDBName' Declare @SPID int --get all processes DECLARE @Table TABLE( SPID INT, Status VARCHAR(MAX), LOGIN VARCHAR(MAX), HostName VARCHAR(MAX), BlkBy VARCHAR(MAX), DBName VARCHAR(MAX), Command VARCHAR(MAX), CPUTime INT, DiskIO INT, LastBatch VARCHAR(MAX), ProgramName VARCHAR(MAX), SPID_1 INT, REQUESTID INT ) INSERT INTO @Table EXEC sp_who2 --using cursor to kill all processes Declare cur_KillAllProcesses CURSOR FAST_FORWARD FOR Select SPID From @Table WHERE DBName=@DbName OPEN cur_KillAllProcesses FETCH NEXT FROM cur_KillAllProcesses INTO @SPID WHILE @@FETCH_STATUS=0 BEGIN --add kill process command Exec('KILL '+ @SPID) FETCH NEXT FROM cur_KillAllProcesses INTO @SPID END CLOSE cur_KillAllProcesses DEALLOCATE cur_KillAllProcesses