Script to kill all connections to a database (More than RESTRICTED_USER ROLLBACK)

Question

I have a development database that re-deploy frequently from a Visual Studio Database project (via a TFS Auto Build).

Sometimes when I run my build I get this error:

ALTER DATABASE failed because a lock could not be placed on database 'MyDB'. Try again later.  

ALTER DATABASE statement failed.  

Cannot drop database "MyDB" because it is currently in use.  

I tried this:

ALTER DATABASE MyDB SET RESTRICTED_USER WITH ROLLBACK IMMEDIATE

but I still cannot drop the database. (My guess is that most of the developers have dbo access.)

I can manually run SP_WHO and start killing connections, but I need an automatic way to do this in the auto build. (Though this time my connection is the only one on the db I am trying to drop.)

Is there a script that can drop my database regardless of who is connected?

Answer 1

You can use the below query, for MS SQL Server 2000, 2005, 2008.

QUERY:

USE master;

DECLARE @kill varchar(8000); SET @kill = '';  

SELECT @kill = @kill + 'kill ' + CONVERT(varchar(5), spid) + ';'  

FROM master..sysprocesses  

WHERE dbid = db_id('MyDB')

EXEC(@kill); 

You can try using the below code, for MS SQL Server 2012 and above. 

QUERY:

USE [master];

DECLARE @kill varchar(8000) = '';  

SELECT @kill = @kill + 'kill ' + CONVERT(varchar(5), session_id) + ';'  

FROM sys.dm_exec_sessions

WHERE database_id  = db_id('MyDB')

EXEC(@kill);