How can I detach a database that is in use?

Question

Even though nothing is using it I am unable to detach a database because it is use.

Cannot detach the database 'DEMO' because it is currently in use.

I have tried rebooting and get the same message.

score 17 · Accepted Answer · answered Oct 20 '09 at 18:05

--Kick all users off of the database NOW
ALTER DATABASE YourDatabase SET SINGLE_USER WITH ROLLBACK IMMEDIATE

--Kick all but after 60 seconds
ALTER DATABASE YourDatabase SET SINGLE_USER WITH ROLLBACK AFTER 60 SECONDS

--restore connection to users
ALTER DATABASE YourDatabase SET MULTI_USER

score 13 · Answer 2 · answered Oct 20 '09 at 17:33

13

Have you tried checking the "Drop Connections" box when you detach it?

answered Oct 20 '09 at 17:33

joeqwerty

111,849

score 1 · Answer 3 · edited Feb 03 '15 at 09:58

1

Disconnect from server.
Restart the SQL Server service to close all connections.
Log in with Windows Authentication.
Easily detach the database.

edited Feb 03 '15 at 09:58

Andrew Schulman

9,142

answered Feb 03 '15 at 09:48

MSS

201

score 1 · Answer 4 · edited Jul 12 '17 at 13:53

1

Don't forget to check "Drop Connections" from "Detach Database" window, otherwise it's very simple and just choose detach from the menu, see the image below:

edited Jul 12 '17 at 13:53

Pierre.Vriens

1,153

answered Jul 12 '17 at 12:35

Ashraf El-Maadidi

11

score 0 · Answer 5 · answered Oct 20 '09 at 17:58

0

This command should drop all connections, then allow you to detach.

ALTER DATABASE SET SINGLE_USER WITH ROLLBACK_IMMEDIATE

answered Oct 20 '09 at 17:58

DanBig

11,445

How can I detach a database that is in use?

5 Answers5