Click here to Skip to main content
15,749,281 members
Articles / Programming Languages / SQL
Posted 22 May 2012

Tagged as


32 bookmarked

How to find the active SQL connections and kill them

Rate me:
Please Sign up or sign in to vote.
3.45/5 (12 votes)
22 May 2012CPOL
Code that will show you the active connections to a database.


Many a times it becomes impossible to find out the active connections to a SQL Server database. That could create a lot of irritation while doing database activities like restoring a database. Below is the code that will show you the active connections to a database.

How to find the active SQL connections  

    db_name(dbid) as [Database Name], 
    count(dbid) as [No Of Connections],
    loginame as [Login Name]
    dbid > 0
group by 
    dbid, loginame

How to kill a SQL connection 

set nocount on
declare @databasename varchar(100)
declare @query varchar(max)
set @query = ''

set @databasename = 'xxx'
if db_id(@databasename) < 4
	print 'system database connection cannot be killeed'

select @query=coalesce(@query,',' )+'kill '+convert(varchar, spid)+ '; '
from master..sysprocesses where dbid=db_id(@databasename)

if len(@query) > 0
print @query

Suggessions and comments are welcomed. It's always fun to know new ways of doing things.


This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Written By
Software Developer (Senior)
India India
Im is a Senior Software Developer working with a software company in Mumbai(India). He has overall 5.5 years of experience in .net technology. He has knowledge in C# 3.0, SQL Server 2005, SQL Reporting service, Enterprise Library 3.0, WCSF & Windows Workflow Foundation.

He has a hands on cutting edge tool like MS Visio, Rational Rose, Borland together 2006 & CruiseControl.Net

Currently his area of interest is on LINQ and Sharepoint.

He is MCPD-EA Certified.

Comments and Discussions

QuestionThanks for query Pin
Member 114217302-Feb-15 11:21
Member 114217302-Feb-15 11:21 
Questionsp_who Pin
JonathanFahey29-May-12 2:34
JonathanFahey29-May-12 2:34 
SuggestionDangerous Pin
Jan Steyn22-May-12 21:02
Jan Steyn22-May-12 21:02 
GeneralRe: Dangerous Pin
Gautam Sharma30-May-12 9:58
Gautam Sharma30-May-12 9:58 
GeneralRe: Dangerous Pin
hariseos14-May-15 9:09
hariseos14-May-15 9:09 
GeneralRe: Dangerous Pin
harsimranb3-Nov-15 8:50
harsimranb3-Nov-15 8:50 
I agree, but the person running this code will need to have relevant permissions to run this query, and will likely be the admin.

Regardless, it's a developer's duty to know the query he/she is running and the repercussions of it. Absolutely nothing wrong with this article.

My vote of 5!

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.