change to database to 'online' and set db to 'multi-user'

Question

I have a 2008 sql database that is offline that I would like to take online and set to multi-user. Using sql server management studio - new query window - when i execute the following:

   ALTER DATABASE mydb SET ONLINE;

    ALTER DATABASE mydb SET MULTI_USER;

I receive this error message:

Msg 5064, Level 16, State 1, Line 1 Changes to the state or options of database 'mydb' 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, Line 1 ALTER DATABASE statement failed. Msg 5064, Level 16, State 1, Line 3 Changes to the state or options of database 'mydb' 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, Line 3 ALTER DATABASE statement failed.

How would I get the database online and in multi-user mode?

Answer 1

a user is currently connected to it

^ This is the problem you need to solve.

Make sure you are not IN that database. Close any query windows that are connected to it, shut down Object Explorer Details, close SSMS and re-open it without Object Explorer connected to that server, etc. Run this:

USE [master];
GO

ALTER DATABASE mydb SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
GO
USE mydb;
GO

That should allow you to bring it online, then you would run the commands you listed.

However:

• This can take longer than you might, depending on what rollback activity has to happen to the sessions you're kicking out.
• It's always possible that when you set it to single user, another process can be faster than you and take that single connection. If you find that this happens, you can figure out who it is using sp_whoisactive or DMVs like sys.dm_tran_locks - it may be that you need to suspend connecting applications and/or shut down SQL Server Agent.