Friday, January 18, 2008

Kill All Database Connections to a SQL Server Database

Today I needed to kill the active connections to a particular database so I can delete it. Usually that can be a painful process of opening enterprise manager and killing each active process one by one. While that approach might be fine for one or two active connections, when you have over 10, it gets to be annoying. So here is a script that will get all the active connections for a given database and kill each one. This is already part of my other post titled SQL Server Script to Restore a Database from File.
-- Create the sql to kill the active database connections
declare @execSql varchar(1000), @databaseName varchar(100)
-- Set the database name for which to kill the connections
set @databaseName = 'myDatabase'

set @execSql = '' 
select  @execSql = @execSql + 'kill ' + convert(char(10), spid) + ' '
from    master.dbo.sysprocesses
where   db_name(dbid) = @databaseName
     DBID <> 0
     spid <> @@spid
As Noel pointed out, it turns out there is much simpler way:
alter database dbName set single_user with rollback immediate
and to revert that
alter database dbName set multi_user with rollback immediate
// //]]>