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?

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

Solution 5

This solution worked for me.

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

View more solutions

Comments

[Ali Mst]

Oddly enough it was USE master that was the key. I was trying to drop the db while connected to it (Duh!). Thanks!

[Mark Henderson]

This is the better answer of the two; avoids taking the database offline and the accepted answer doesn't always work (sometimes it can't roll everything back).

[mattalxndr]

If you use SET OFFLINE you have to manually delete the db files.

[Keith]

Wouldn't alter database YourDatabaseName set SINGLE_USER with rollback immediate be better? If you set it to OFFLINE (as @mattalxndr states) the files will be left on disk, but with SINGLE_USER your connection will be left as the only one, and drop database YourDatabaseName will still remove the files.

[Saeed Neamati]

Such a nice answer for aggregating kill statements all together. I would use a cursor to kill each process, which of course is not efficient at all. The technique used in this answer is brilliant.

[ivan_pozdeev]

I had issues with set SINGLE_USER when there already was a single active connection.

[ivan_pozdeev]

@Keith in the script, you aren't connected to the DB, so it's not "your connection" but some other that will be left. Immediately after the set offline, you can issue set online to avoid the leftover files problem (yes, there's a race condition possibility).

[eythort]

Thanks! I didn't realize that some tab with sql statement in SQL Management Studio, executed earlier on this database, was causing my db to be reported in use. Use master, and go, made everything work!

[Austin S.]

I agree with Mark. This method should be the accepted answer as it is significantly more elegant and less impacting for the databases.

[Saurabh Sinha]

I will not recommend taking databse in single user mode for any of users as this can cause you loose current connection to some application user and unnecesarry trouble to find those users and kill same or some times u have to restart sql server if connections to db are so frequent.

[Saurabh Sinha]

good one and quick. Only problem could be system spid so u can add WHERE dbid = db_id('My_db') and spid > 50

[FrenkyB]

How do you solve problem 'you can not kill your own process' ? I have to open a query window to execute new query. So I guess this is 'my own process', my own connection to the database.

[AlexK]

@FrenkyB You need to change the database context before run script. For ex.: USE [Master]

[Alex Kwitny]

@SaurabhSinha - see sqlblog.com/blogs/adam_machanic/archive/2010/06/23/… - spid>50 not the best

[Eamon Nerbonne]

@Keith Actually, even single user mode is buggy (that's really the only word for it): it ensures no other "normal" users can connect, but built-in processes such as those to auto-update statistics asynchronously are not disabled, and when they run, they'll highjack the single connection, killing your process. If there are any asynchronous processes that might run, there's no reliable way to enforce single user mode, alas - but set offline does work. I learned this the hard way on our CI, which used single user mode regularly as part of schema migration testing.

[Matthew Hintzen]

This was exactly what I needed, this is the better answer in my opinion

[Rastographics]

I used this code exactly, and I get error: "Incorrect syntax near 'Go'." Does anyone know why this would happen?

[Ross Presser]

If the SINGLE_USER command is in the same batch as your (scripted) restore command -- not separated by a GO statement! -- then no other process can grab single user access, in my experience. However, I was caught tonight because my nightly scheduled job of set-single-user;restore;set-multi-user blew up. another process had exclusive file access to my bak file (smh) and therefore the restore failed, followed by the SET MULTI_USER failing ... meaning when I got called in the middle of the night to clean up the blood, someone else had SINGLE_USER access and had to be killed.

[Adwaenyth]

The database_id column is not available in SQL Server 2008. It's only available from SQL Server 2012 onwards. See MSDN for reference.

[DeanOC]

The SQL 2000/2005 script doesn't work, as you cannot assign a value to a variable at the time of declaring it (that functionality was introduced in 2008). Changing the first line to DECLARE @kill varchar(8000) and adding SET @kill = '' works

[HansLindgren]

@FrenkyB: Add AND session_id!=@@SPID to the WHERE clause to not 'Kill' yourself...

[Arun Prasad E S]

Had to restart all my Domains

[Dan Guzman]

Aggregate string concatenation (@kill = @kill + …) isn't supported and may yield unexpected results. It would be best to refactor using FOR XML PATH or, in SQL 2017 and later. STRING_AGG.

[diceless]

GO is not a TSQL command but a special command only recognized by the sqlcmd and osql utilities and SSMS.

[Bastien Vandamme]

I tried this and I get a "Only user processes can be killed." error message. what should I do?

[AlexK]

@BastienVandamme You can try WHERE dbid = db_id('My_db') and spid > 50

[dudeNumber4]

I want to kill all connections except mine. Added and session_id != @@SPID to where clause.

[deroby]

THIS! Although I personally use the sys.dm_tran_locks table as syslockinfo is marked obsolete, Also, you may want to exclude your current @@SPID just in case.