Click here to Skip to main content
65,938 articles
CodeProject is changing. Read more.
Articles / Languages / SQL

How to find the active SQL connections and kill them

3.45/5 (12 votes)
22 May 2012CPOL 197.1K  
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)