Click here to Skip to main content
15,562,222 members
Articles / Programming Languages / SQL
Article
Posted 22 May 2012

Tagged as

Stats

193.3K views
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.

Introduction

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  

SQL
select 
    db_name(dbid) as [Database Name], 
    count(dbid) as [No Of Connections],
    loginame as [Login Name]
from
    sys.sysprocesses
where 
    dbid > 0
group by 
    dbid, loginame

How to kill a SQL connection 

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

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

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

if len(@query) > 0
begin
print @query
	exec(@query)
end

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

License

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 12:21
Member 114217302-Feb-15 12:21 
Questionsp_who Pin
JonathanFahey29-May-12 3:34
JonathanFahey29-May-12 3:34 
SuggestionDangerous PinPopular
Jan Steyn22-May-12 22:02
Jan Steyn22-May-12 22:02 
GeneralRe: Dangerous Pin
Gautam Sharma30-May-12 10:58
Gautam Sharma30-May-12 10:58 
Well for me just the requirement was to kill all the open connections before restoring test database.(that always use to give me an error - not exactly know what was the error.) the above query helped me a lot to kill all the open connection for smooth restoration.
Happy coding

GeneralRe: Dangerous Pin
hariseos14-May-15 10:09
hariseos14-May-15 10:09 
GeneralRe: Dangerous Pin
harsimranb3-Nov-15 9:50
harsimranb3-Nov-15 9: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.