Click here to Skip to main content
15,742,609 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 PinPopular
Jan Steyn22-May-12 21:02
Jan Steyn22-May-12 21:02 
Your code to kill a connection, will kill all connections to the specified database. Whilst this might be fine in a development environment, in a production environment it could lead to dataloss.

If a connection is blocking others, it is important to first find out what exactly that process is trying to accomplish and why it is blocking other processes. An example could be the staff salary run blocking other processes in an accounting database. Would you really just want to kill that process? Could other users wait for it to finish?
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 

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.