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

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?

Abortion

Answer 1

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

Koenig

That doesn't seem to work for SQL Server 2008... Here is the error I got: Console: Msg 102, Level 15, State 1, Line 4 Incorrect syntax near '-'. Msg 319, Level 15, State 1, Line 4 Incorrect 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 4 Incorrect syntax near 'IMMEDIATE'. Command: ALTER DATABASE ASMR-wdanda SET SINGLE_USER WITH ROLLBACK IMMEDIATE – Telemark

I just ran this on 2008 without problems ALTER 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? – Koenig

Worked for me with SQL Server 2008 and SQL Express instance. – Rayerayfield

@Wagner if the database has a '-' in the name you need to use brackets around it: ALTER DATABASE [foo-bar] SET SINGLE_USER WITH ROLLBACK IMMEDIATE – Homochromatic

Hello all, I tried this. Now I am unable to connect back to the DB to set multi_user. Using Squirrel SQL with microsoft sqljdbc driver. Please help. – Geostatic

Please Note - Do NOT try this on the SQL Server hosted on Amazon RDS. You will be unable to reset the DB back to MULTI_USER mode. Make sure you have another set of DBA credentials before you attempt this. I fixed this by reverting to one of the previous snapshots. Lost some data. Luckily the data wasn't critical. – Geostatic

Answer 2

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

Abortion

This worked for me, I added and spid <> @@SPID to SELECT @sKillConnection statement so that it wouldn't try to kill my current connection, which would generate an error message. – Cockney

Only user processes can be killed... still deadlocked and can't restore multi_user mode due to deadlock. – Marshland

mateuscb- the only way it won't work on mssql 10.00 is if you have a database name that requires [] and you don't use them. ALTER DATABASE [YourDatabase] SET SINGLE_USER WITH ROLLBACK IMMEDIATE works in 10, 10.5, 11 and 12. – Wohlen

Answer 3

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

Rewire

Answer 4

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.

Dronski

I don't see this "Activity Monitor" item under Management... Again, maybe it's because I'm using SQL 2008? – Telemark

I've found an "Activity Montior" if you right click the SERVER, not the DB. You can then select the 'Processes' tab and filter by Database. – Bang

You apparently need to kill stalled process one by one but it's a straightforward method that doesn't require local login or bringing the complete database server down. – Nuncupative

Answer 5

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 

Snakebite

Answer 6

ALTER DATABASE [Test]
SET OFFLINE WITH ROLLBACK IMMEDIATE

ALTER DATABASE [Test]
SET ONLINE

Aesculapius

Answer 7

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

Fenella

Like it. Quickest way of doing it from the GUI for sure. – Mariellamarielle

It works like a charm! The easy way is the good way. Thanks. – Crist

Answer 8

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.

Windjammer

It really works :) I would advise though, to keep the execute part of this script commented out and put a print @query instead, just to be sure you don't run this on a production server by mistake. – Pion

Answer 9

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.

Pursuance

Thanks, this worked (the ALTER DATABASE ... SET SINGLE_USER commands in other answers returned the same 'could not get exclusive lock' error). – Nicollenicolson

Answer 10

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'

Commutator

You can't do this if there's an active connection. – Bang

Answer 11

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.

Bang

Answer 12

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)

Bernt

What do you mean 'not recommended'? If you aren't concerned about any connections to that server (ie: debug or staging environments, for example - or a production server with temp. downtime) this may be the easiest way. For production - you don't want to be mucking with configuration if you can just restart the service. What would you do? – Bernt

I would go for anything that should affect ONLY my target DB. your approach of killing all DBs on the target server is not that smart. but to be honest, in staging environments, this maybe the easiest way as you said. – Bloodyminded

Answer 13

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.

Kandis

In SQL 2008 Management Studio, you for some reason can no longer access the active connection from the "Detach" screen. It works great in 2005 and this is how I always did it, until we upgraded to 2008 and now all you get is a stupid message that tells you to close your connection, but doesn't let you open the connection details to kill each connection. – Britteny

Answer 14

Try this:

ALTER DATABASE [DATABASE_NAME]
SET SINGLE_USER
WITH ROLLBACK IMMEDIATE

Methane

Answer 15

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

Muck

Answer 16

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.

Yahoo

Answer 17

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.

Basiliabasilian

Answer 18

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;

Aramanta

Answer 19

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

Zarzuela