How to kill/Terminate all running process on Sql Server 2008

sql-server-2008 database-deadlocks
Buzz picture Buzz · Jun 11, 2012 · Viewed 62.3k times · Source

After executing this query on master db ,it is giving me all running process on all databases, is there any query which will kill all process running on a database .

USE
Master
GO

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

Answer

Damien_The_Unbeliever picture Damien_The_Unbeliever · Jun 11, 2012

If you want to force every other connection to disconnect, and you have suitable permissions, you can bounce the database in 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 terminated.

Related questions

What does "lock | communication buffer resources" mean?

I have an error log which reports a deadlock: Transaction (Process ID 55) was deadlocked on lock | communication buffer resources with another process and has been chosen as the deadlock victim. Rerun the transaction. I am trying to reproduce this error, …

Read More
Trigger causing a deadlock?

I'm running into a deadlock after I added a trigger. There is a UserBalanceHistory table which has one row for each transaction and an Amount column. A trigger was added to sum the Amount column and place the result in …

Read More
How to solve Index/Key related Deadlock

I have diagnosed a deadlock problem in my sql server using the deadlock graph in SQL Server 2008. The problem has to do with my indexes. I have two queries: a long running report with alot of joins and subqueries that …

Read More