How to kill active connections based on DB_NAME using SQL?

Question

I am currently connected to a customer database via the front end using there application and I run the following query which displays active connections:

SELECT DB_NAME(DBID) AS DBNAME,
       COUNT(DBID)   AS NUMBEROFCONNECTIONS,
       LOGINAME      AS LOGINNAME
FROM   SYS.SYSPROCESSES
WHERE  DBID > 0
GROUP  BY DBID, LOGINAME

I have the ability to run SQL in the front end of the application therefore how can i kill all active connections based on a DB_NAME(DBID) = 'CustomerDB' using SQL?

I found an answer on a single click, did you really try to research ? — japzdivino, Oct 06 '15 at 09:32

score 0 · Answer 1 · answered Oct 06 '15 at 09:35

0

KILL command may not work for the newly established connection. You can try taking the database to Single_User mode rather using an ALTER statement like

ALTER DATABASE CustomerDB
SET SINGLE_USER WITH ROLLBACK IMMEDIATE

answered Oct 06 '15 at 09:35

Rahul

76,197
13
71
125

score -1 · Answer 2 · edited May 23 '17 at 12:29

-1

USE master
GO

DECLARE @kill varchar(8000) = '';
SELECT @kill = @kill + 'kill ' + CONVERT(varchar(5), spid) + ';'
FROM master..sysprocesses 
WHERE dbid = db_id('MyDB')

EXEC(@kill);

Credits: Script to kill all connections to a database (More than RESTRICTED_USER ROLLBACK)

edited May 23 '17 at 12:29

Community

1
1

answered Oct 06 '15 at 09:31

japzdivino

1,736
3
17
25

This should be flagged as duplicate, not posted as your own answer. – Evaldas Buinauskas Oct 06 '15 at 09:32
@EvaldasBuinauskas , yup i posted it because it seems that the person who ask did not try to research on his own, so im trying to help.. that's why i put credits.. yup i also flagged this question as duplicate. – japzdivino Oct 06 '15 at 09:34
@Japz Divino: You are rude – PriceCheaperton Oct 06 '15 at 10:18
@PriceCheaperton my bad, because i tried to research when i read your question, and i found an answer here also in stackoverflow site, so i was wondering if you really tried to search. anyways my apologies if i offended you in my comment. – japzdivino Oct 06 '15 at 10:26
No probs................ – PriceCheaperton Oct 06 '15 at 10:53

How to kill active connections based on DB_NAME using SQL?

2 Answers2