Click here to Skip to main content
13,666,464 members
Click here to Skip to main content
Add your own
alternative version

Tagged as


31 bookmarked
Posted 22 May 2012
Licenced CPOL

How to find the active SQL connections and kill them

, 22 May 2012
Rate this:
Please Sign up or sign in to vote.
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)


About the Author

Gautam Sharma
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.

You may also be interested in...


Comments and Discussions

QuestionThanks for query Pin
Member 114217302-Feb-15 11:21
memberMember 114217302-Feb-15 11:21 
GeneralMessage Removed Pin
29-Oct-14 21:58
memberMember 1045369129-Oct-14 21:58 
Questionsp_who Pin
JonathanFahey29-May-12 2:34
memberJonathanFahey29-May-12 2:34 
SuggestionDangerous Pin
Jan Steyn22-May-12 21:02
memberJan Steyn22-May-12 21:02 
GeneralRe: Dangerous Pin
Gautam Sharma30-May-12 9:58
memberGautam Sharma30-May-12 9:58 
GeneralRe: Dangerous Pin
hariseos14-May-15 9:09
memberhariseos14-May-15 9:09 
GeneralRe: Dangerous Pin
harsimranb3-Nov-15 8:50
memberharsimranb3-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.

Permalink | Advertise | Privacy | Cookies | Terms of Use | Mobile
Web05-2016 | 2.8.180810.1 | Last Updated 22 May 2012
Article Copyright 2012 by Gautam Sharma
Everything else Copyright © CodeProject, 1999-2018
Layout: fixed | fluid