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

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?

Solution 1:

Updated

For MS SQL Server 2012 and above

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);

For MS SQL Server 2000, 2005, 2008

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); 

Solution 2:

USE master
GO
ALTER DATABASE database_name
SET OFFLINE WITH ROLLBACK IMMEDIATE
GO

Ref: http://msdn.microsoft.com/en-us/library/bb522682%28v=sql.105%29.aspx

Solution 3:

You can get the script that SSMS provides by doing the following:

1. Right-click on a database in SSMS and choose delete
2. In the dialog, check the checkbox for "Close existing connections."
3. Click the Script button at the top of the dialog.

The script will look something like this:

USE [master]
GO
ALTER DATABASE [YourDatabaseName] SET  SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
USE [master]
GO
DROP DATABASE [YourDatabaseName]
GO

Solution 4:

Little known: the GO sql statement can take an integer for the number of times to repeat previous command.

So if you:

ALTER DATABASE [DATABASENAME] SET SINGLE_USER
GO

Then:

USE [DATABASENAME]
GO 2000

This will repeat the USE command 2000 times, force deadlock on all other connections, and take ownership of the single connection. (Giving your query window sole access to do as you wish.)