CODEWITHSUNDEEP
sqlsql-serversql-server-2008
Alex Gordon
Alex Gordon

Reputation: 60691

sql server 2008 how do i disconnect everyone from my db?

I need to rename the database, but I cannot since other processes is using it. I don't care about the process, and I need to kill it.

How do I remove all connections from the db?

Upvotes: 13

Views: 21114

Answers (4)

David Fawzy
David Fawzy

Reputation: 1076

This worked fine for me to rename database and force disconnect everybody:

ALTER DATABASE [old_name]
    SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
ALTER DATABASE [old_name]
    MODIFY NAME = [new_name]
GO
ALTER DATABASE [new_name]
    SET MULTI_USER
GO

Upvotes: 0

Martin Smith
Martin Smith

Reputation: 452988

As per my comment once in single_user mode you need to do the rename from the same connection. Don't try and rename it through Object Explorer as that will try and open up a new connection. The following works this end...

ALTER DATABASE AdventureWorks SET single_user WITH ROLLBACK IMMEDIATE

ALTER DATABASE AdventureWorks MODIFY NAME = NewAdventureWorks

ALTER DATABASE NewAdventureWorks SET multi_user

Upvotes: 24

SQLMenace
SQLMenace

Reputation: 134941

Like this: Kill All Active Connections To A Database

ALTER DATABASE oldNameSET SINGLE_USER WITH ROLLBACK IMMEDIATE

EXEC sp_renamedb 'oldName', 'newName'

ALTER DATABASE newName SET MULTI_USER  --new name of course

Upvotes: 9

Stu
Stu

Reputation: 15769

Run sp_who, then kill [pid] for everyone in your database.

Upvotes: 2

Related Questions