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 and DBID <> 0 and spid <> @@spid exec(@execSql)Update
As Noel pointed out, it turns out there is much simpler way:
alter database dbName set single_user with rollback immediateand to revert that
alter database dbName set multi_user with rollback immediate