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

Question

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   

Arpeggio

"which will kill all process running on a database" - now why on earth would anyone want to do that! – Miscellany

@mitch: we are testing on our dummy databases ,so no need to worry – Arpeggio

To what end? If you're wanting to e.g. go to single user mode, there are ways to force everyone else to disconnect (ROLLBACK IMMEDIATE) – Burletta

"we are testing on our dummy databases" - that still doesn't mean its not a really idiotic thing to do in production. Perhaps you should tell us the real problem..... – Miscellany

You could reboot the DB server – Mainsail

@mitch: yeh your concern is correct ,but the real problem is one process is creating deadlocks on the tables, so to rectify the issue i need to kill process – Arpeggio

No! You need to eliminate the deadlocks. Treat the cause NOT the symptoms. Most deadlocking can be fixed with appropriate indexes. In the future, suggest ask about your real problem, rather than the implementation of a perceived solution. – Miscellany

@Alex: Telling someone to reboot a SQL Server is rarely the right advice... – Miscellany

@MitchWheat We're talking Microsoft, rebooting very often solves problems when everything else fails. Jokes aside, I misunderstood and thought it was a one-time issue... I strongly agree with your "treat the cause not the symptoms" suggestion. – Mainsail

Answer 1

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.

Burletta

"ALTER DATABASE current..." is only supported from SQL Server 2012 and forward – Congdon

@Congdon - yes, current is only supported in 2012. For earlier versions you have to name the database. – Burletta

You just saved my life! – Bald

Answer 2

Please see below 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   

Advancement

Answer 3

You can use the KILL statement combined with a cursor on the above query result.

See Kill (Transact-SQL)

Desrochers