Thursday, June 27, 2013

Find number of active SQL connections and kill them

How to find number of 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 active SQL connections to a single database

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

set @databasename = 'My database'
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


Link to article
http://www.codeproject.com/Articles/389725/How-to-find-the-active-SQL-connections-and-kill-th

1 comment: