Home / server / Questions / 76432
Accepted
Jack B Nimble
Asked: 2009-10-21 09:29:52 +0800 CST

How can I detach a database that is in use?

  • 772

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.

sql-server-2005

5 Answers

  1. Best Answer
    --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
    • 17

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

    • 12
    1. Disconnect from server.
    2. Restart the SQL Server service to close all connections.
    3. Log in with Windows Authentication.
    4. Easily detach the database.
    • 1

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

    enter image description here

    • 1

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

    ALTER DATABASE SET SINGLE_USER WITH ROLLBACK_IMMEDIATE

    • 0