How do you kill all current connections to a SQL Server 2005 database?

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.

The option working for me in this scenario is as follows:

1. Start the "Detach" operation on the database in question. This wil open a window (in SQL 2005) displaying the active connections that prevents actions on the DB.
2. Kill the active connections, cancel the detach-operation.
3. The database should now be available for restoring.

Select 'Kill '+ CAST(p.spid AS VARCHAR)KillCommand into #temp
from master.dbo.sysprocesses p (nolock)
join master..sysdatabases d (nolock) on p.dbid = d.dbid
Where d.[name] = 'your db name'

Declare @query nvarchar(max)
--Select * from #temp
Select @query =STUFF((
select ' ' + KillCommand from #temp
FOR XML PATH('')),1,1,'')
Execute sp_executesql @query
Drop table #temp

use the 'master' database and run this query, it will kill all the active connections from your database.

These didn't work for me (SQL2008 Enterprise), I also couldn't see any running processes or users connected to the DB. Restarting the server (Right click on Sql Server in Management Studio and pick Restart) allowed me to restore the DB.

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](

[To see links please register here]

) solution responded with error. [Here is one that worked in my case](

[To see links please register here]

).

ALTER DATABASE [Test]
SET OFFLINE WITH ROLLBACK IMMEDIATE

ALTER DATABASE [Test]
SET ONLINE

You can Use SP_Who command and kill all process that use your database and then rename your database.

I use sp_who to get list of all process in database. This is better because you may want to review which process to kill.

declare @proc table(
SPID bigint,
Status nvarchar(255),
Login nvarchar(255),
HostName nvarchar(255),
BlkBy nvarchar(255),
DBName nvarchar(255),
Command nvarchar(MAX),
CPUTime bigint,
DiskIO bigint,
LastBatch nvarchar(255),
ProgramName nvarchar(255),
SPID2 bigint,
REQUESTID bigint
)

insert into @proc
exec sp_who2

select *, KillCommand = concat('kill ', SPID, ';')
from @proc

**Result**
You can use command in KillCommand column to kill the process you want to.

SPID KillCommand
26 kill 26;
27 kill 27;
28 kill 28;

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.
5. Bring the DB back online.
6. Rename the DB.
7. Bring your service back online and point it to the new DB.

The reason that the approach that [Adam suggested](

[To see links please register here]

) 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. You could instead use 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