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 12 · Answer 2 · answered Oct 20 '09 at 17:33

12

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

answered Oct 20 '09 at 17:33

joeqwerty

108,377
6
80
171

I was hoping more for a query related solution. Server Management Studio Express very often causes my machine to blue screen. So I'm using a tool that just sends queries to the server. – Jack B Nimble Oct 20 '09 at 17:54
A blue screen is usually related to hardware or OS-level problems, not to some very specific application such as a database management tool. I can't imagine why this particular program makes your machine crash, but this is definitely a symptom of some underlying serious problem. – Massimo Oct 20 '09 at 18:53
It is some combination of Visual Studio and Server Management Studio Express, because I have seen the behavior on 3 different workstations. It might be from running multiple versions of Visual Studio at once, I know this causes bizarre behavior in IE7 when using tabs. – Jack B Nimble Oct 28 '09 at 18:30

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

8,561
21
31
47

answered Feb 03 '15 at 09:48

MSS

181
1
3

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,159
34
15
19

answered Jul 12 '17 at 12:35

Ashraf El-Maadidi

11
2

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,393
1
28
53

How can I detach a database that is in use?

5 Answers5