views:

10051

answers:

11

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?

+4  A: 

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'

John Christensen
+6  A: 

See Kill All Active Connections To A Database

SQLMenace
That doesn't seem to work for SQL Server 2008... Here is the error I got:Console:Msg 102, Level 15, State 1, Line 4Incorrect syntax near '-'.Msg 319, Level 15, State 1, Line 4Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon.Msg 102, Level 15, State 1, Line 4Incorrect syntax near 'IMMEDIATE'.Command:ALTER DATABASE ASMR-wdanda SET SINGLE_USER WITH ROLLBACK IMMEDIATE
Wagner Danda da Silva
I just ran this on 2008 without problemsALTER DATABASE aspnetdb SET SINGLE_USER WITH ROLLBACK IMMEDIATE select GETDATE() ALTER DATABASE aspnetdb SET MULTI_USER what do you have instead of the commented out code?
SQLMenace
Worked for me with SQL Server 2008 and SQL Express instance.
Tim Murphy
+1  A: 

Why not take the database offline, then rename it, put back online.

Kev
That worked, thanks! I just had to restart my SQL Management Studio because of this bug: Database in Transition http://social.msdn.microsoft.com/Forums/en-US/sqlgetstarted/thread/e779230a-87bc-4cee-b223-98a029097dc1?prof=required
Wagner Danda da Silva
+1  A: 

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
Adam
A: 

Using SQL Management Studio Express:

In the Object Explorer tree drill down under Management to "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.

Joseph Daigle
I don't see this "Activity Monitor" item under Management... Again, maybe it's because I'm using SQL 2008?
Wagner Danda da Silva
+1  A: 

Try this:

ALTER DATABASE [DATABASE_NAME]
SET SINGLE_USER
WITH ROLLBACK IMMEDIATE
Joseph Sturtevant
+1  A: 

Adam the code you posted won't work because durin the time that you are looping over the active connections new ones can be connected and you will miss those. Take a look at the link I provided which has the following code

ALTER DATABASE YourDatabase SET SINGLE_USER WITH ROLLBACK IMMEDIATE 

--do you stuff here 

ALTER DATABASE YourDatabase SET MULTI_USER
SQLMenace
A: 

I've always used:


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 
brendan
A: 

Hit the power button

Terrapin
That's a good one! LOL
Wagner Danda da Silva
+1  A: 

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.

RedWolves
Thanks, this worked (the `ALTER DATABASE ... SET SINGLE_USER` commands in other answers returned the same 'could not get exclusive lock' error).
Tinister
A: 

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
btk