65.9K
CodeProject is changing. Read more.
Home

How to find the active SQL connections and kill them

starIconstarIconstarIcon
emptyStarIcon
starIcon
emptyStarIcon

3.45/5 (12 votes)

May 22, 2012

CPOL
viewsIcon

197132

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.