[SOLVED] Exit single-user mode

Exit single-user mode

Currently, my database is in Single User mode. When I try to expand me database, I get an error:

The database 'my_db' is not accessible.(ObjectExplorer)

Also, when I try to delete the database, I get the error:

Changes to the state or options of database 'my_db' cannot be made at this time. The database is in single-user mode, and a user is currently connected to it.

How do I exit out of single-user mode? I don't have any user using this database.

When I try to browse my site with IIS, the error I get is:

An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.

I feel as though the single-user mode is causing this.

Solution

SSMS in general uses several connections to the database behind the scenes. You will need to kill these connections before changing the access mode:

First, make sure the object explorer is pointed to a system database like master.
Second, execute a sp_who2 and find all the connections to database 'my_db'. Kill all the connections by doing KILL { session id } where session id is the SPID listed by sp_who2.
Third, open a new query window.

After those 3 steps, execute the following code.

-- Start in master
USE MASTER;

-- Add users
ALTER DATABASE [my_db] SET MULTI_USER
GO

See my blog article on managing database files. This was written for moving files, but user management is the same.