How do you kill all current connections to a SQL S

Question

I want to rename a database, but keep getting the error that 'couldn't get exclusive lock' on the database, which implies there is some connection(s) still active.

How can I kill all the connections to the database so that I can rename it?

Answer 1

I usually run into that error when I am trying to restore a database I usually just go to the top of the tree in Management Studio and right click and restart the database server (because it's on a development machine, this might not be ideal in production). This is close all database connections.

Answer 2

Here's how to reliably this sort of thing in MS SQL Server Management Studio 2008 (may work for other versions too):

1. In the Object Explorer Tree, right click the root database server (with the green arrow), then click activity monitor.
2. Open the processes tab in the activity monitor, select the 'databases' drop down menu, and filter by the database you want.
3. Right click the DB in Object Explorer and start a 'Tasks -> Take Offline' task. Leave this running in the background while you...
4. Safely shut down whatever you can.
5. Kill all remaining processes from the process tab.
6. Bring the DB back online.
7. Rename the DB.
8. Bring your service back online and point it to the new DB.

Answer 3

I'm using SQL Server 2008 R2, my DB was already set for single user and there was a connection that restricted any action on the database. Thus the recommended SQLMenace's solution responded with error. Here is one that worked in my case.

Answer 4

Try this:

ALTER DATABASE [DATABASE_NAME]
SET SINGLE_USER
WITH ROLLBACK IMMEDIATE

Answer 5

See Kill All Active Connections To A Database.

The reason that the approach that Adam suggested won't work is that during the time that you are looping over the active connections new one can be established, and you'll miss those. The article I linked to uses the following approach which does not have this drawback:

-- set your current connection to use master otherwise you might get an error

use master
ALTER DATABASE YourDatabase SET SINGLE_USER WITH ROLLBACK IMMEDIATE 

--do you stuff here 

ALTER DATABASE YourDatabase SET MULTI_USER

Answer 6

Using SQL Management Studio Express:

In the Object Explorer tree drill down under Management to "Activity Monitor" (if you cannot find it there then right click on the database server and select "Activity Monitor"). Opening the Activity Monitor, you can view all process info. You should be able to find the locks for the database you're interested in and kill those locks, which will also kill the connection.

You should be able to rename after that.