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

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?

In MS SQL Server Management Studio on the object explorer, right click on the database. In the context menu that follows select 'Tasks -> Take Offline'

Script to accomplish this, replace 'DB_NAME' with the database to kill all connections to:


USE master
GO

SET NOCOUNT ON
DECLARE @DBName varchar(50)
DECLARE @spidstr varchar(8000)
DECLARE @ConnKilled smallint
SET @ConnKilled=0
SET @spidstr = ''

Set @DBName = 'DB_NAME'
IF db_id(@DBName) < 4
BEGIN
PRINT 'Connections to system databases cannot be killed'
RETURN
END
SELECT @spidstr=coalesce(@spidstr,',' )+'kill '+convert(varchar, spid)+ '; '
FROM master..sysprocesses WHERE dbid=db_id(@DBName)

IF LEN(@spidstr) > 0
BEGIN
EXEC(@spidstr)
SELECT @ConnKilled = COUNT(1)
FROM master..sysprocesses WHERE dbid=db_id(@DBName)
END

Try this:

ALTER DATABASE [DATABASE_NAME]
SET SINGLE_USER
WITH ROLLBACK IMMEDIATE

I've always used:

<pre><code>
ALTER DATABASE DB_NAME SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
SP_RENAMEDB 'DB_NAME','DB_NAME_NEW'
Go
ALTER DATABASE DB_NAME_NEW SET MULTI_USER -- set back to multi user
GO
</code></pre>

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.

Kill it, and kill it with fire:

USE master
go

DECLARE @dbname sysname
SET @dbname = 'yourdbname'

DECLARE @spid int
SELECT @spid = min(spid) from master.dbo.sysprocesses where dbid = db_id(@dbname)
WHILE @spid IS NOT NULL
BEGIN
EXECUTE ('KILL ' + @spid)
SELECT @spid = min(spid) from master.dbo.sysprocesses where dbid = db_id(@dbname) AND spid > @spid
END

Right click on the database name, click on Property to get property window, Open the Options tab and change the "Restrict Access" property from Multi User to Single User. When you hit on OK button, it will prompt you to closes all open connection, select "Yes" and you are set to rename the database....

Take offline takes a while and sometimes I experience some problems with that..

Most solid way in my opinion:

**Detach**
Right click DB -> Tasks -> Detach...
check "Drop Connections"
Ok

**Reattach**
Right click Databases -> Attach..
Add... -> select your database, and change the Attach As column to your desired database name.
Ok

Another "kill it with fire" approach is to just restart the MSSQLSERVER service.
I like to do stuff from the commandline. Pasting this exactly into CMD will do it:
NET STOP MSSQLSERVER & NET START MSSQLSERVER

Or open "services.msc" and find "SQL Server (MSSQLSERVER)" and right-click, select "restart".

This will "for sure, for sure" kill ALL connections to ALL databases running on that instance.

(I like this better than many approaches that change and change back the configuration on the server/database)