Killing Active SQL Connections

So there are many times we are waiting to take a database offline or delete a database. I finally decided there had to be a better way than to wait … specially when this is my development environment. So here are two queries we can use.

First, find the active connection for all the databases:


select
  db_name(dbid) as [DB Name],
  count(dbid) as [Active Connections],
  loginame as [Login Name]
from sys.sysprocesses
where dbid > 0
group by dbid, loginame;

Then from that, we can determine which database needs the connections terminated. Or you can use this SQL directly if you already know which database you want to kill the active connections on:


set nocount on;
declare @databasename varchar(100);
declare @qry varchar(max);
set @qry = '';
---------------------------------------------------
set @databasename = '[Database name]';
---------------------------------------------------
if db_id(@databasename) < 4 begin print 'Hey, system database connections cannot be killed!!!'; return end select @qry=coalesce(@qry,',' )+'kill '+convert(varchar, spid)+ '; ' from master..sysprocesses where dbid=db_id(@databasename); if len(@qry) > 0
begin
  print @qry;
  exec(@qry);
end

Hope you all find this as useful as I have. Cheers!