My database was in Single User mode. I don’t know how it entered in this mode, but this causes a lot of problem with the database itself. For example, trying to expand the database, you get an error:

The database ‘my_db’ is not accessible.(ObjectExplorer)

Also, when trying to delete it, you get:

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 to fix this?

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

    1. First, make sure the object explorer is pointed to a system database like master.
    2. 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. Here for more info about sp_who2.
      -- Start in master
      USE MASTER;
      EXEC sp_who2
      
    3. Third, open a new query window.Execute the following code.
      -- Start in master
      USE MASTER;
      
      -- Add users
      ALTER DATABASE [my_db] SET MULTI_USER
      GO

NOTE: if you have some problems killing the connections, you can simply restart the service SQL Server {Instance_name}.

Once this procedure is done, it can happen the database enters in the Recovery Pending Status. Just click here to read how to fix this.

Advertisements