Click here to Skip to main content
Click here to Skip to main content

How to find the active SQL connections and kill them

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

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 

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)

Share

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.

Comments and Discussions

 
GeneralMy vote of 2 PinmemberMember 1045369129-Oct-14 22:58 
Questionsp_who PinmemberJonathanFahey29-May-12 3:34 
SuggestionDangerous PinmemberJan Steyn22-May-12 22:02 
GeneralRe: Dangerous PinmemberGautam Sharma30-May-12 10:58 

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

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

| Advertise | Privacy | Terms of Use | Mobile
Web04 | 2.8.141223.1 | Last Updated 22 May 2012
Article Copyright 2012 by Gautam Sharma
Everything else Copyright © CodeProject, 1999-2014
Layout: fixed | fluid