Unable to detach SQL database: "The database is in single-user mode, and a user is currently connected to it."

Scenario:

 

When you attempt to Detach a SQL database, you may possibly end up encountering an error message as below:

C:\>SQLCMD.EXE -U sa -P P@ssword -S TESTDBSRV01\APPDB01 -i “C:\Detach-APP-DB.sql”
Msg 5064, Level 16, State 1, Server TESTDBSRV01\APPDB01, Line 1
Changes to the state or options of database ‘APPDB01’ cannot be made at this time. The database is in single-user mode, and a user is currently connected to it.
Msg 5069, Level 16, State 1, Server TESTDBSRV01\APPDB01, Line 1
ALTER DATABASE statement failed.

C:\>

Where in my C:\Detach-APP-DB.sql file contains below statement:

ALTER DATABASE HRINDDB01 SET OFFLINE WITH ROLLBACK IMMEDIATE

Solution:

 

To resolve this error, you need to ensure that no other users/applications/services/websites are connected to the Database that are holding database from being detached. In my case, I’ve a service that had connections to my database. I had to stop the service for the detach operation to proceed further.

Leave a Reply

Your email address will not be published. Required fields are marked *