The problem

I was trying to “DropCreate” a database using Entity Framework 4.3. Suddenly I get an exception that EF was unable to drop the database. I fired up my SQL Server Mngt Studio and tried to take the database offline. Again, an error message that it couldn’t get an exclusive lock because of open connections.

The solution

After a bit of searching on the SQL Server processes I ended up with this:

USE master
go

DECLARE @dbname sysname
SET @dbname = 'MyDbName'

DECLARE @spid int
SELECT @spid = min(spid) from master.dbo.sysprocesses where dbid = db_id(@dbname)
WHILE @spid IS NOT NULL
BEGIN
EXECUTE ('KILL ' + @spid)
SELECT @spid = min(spid) from master.dbo.sysprocesses where dbid = db_id(@dbname) AND spid > @spid
END

And instantly, it was my! database yet again.