Advertisement
Advertisement


Exit single-user mode


Question

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.

2017/04/07
1
211
4/7/2017 1:18:33 PM

Accepted Answer

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.

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.

2015/03/17
385
3/17/2015 5:36:52 PM

First, find and KILL all the processes that have been currently running.

Then, run the following T-SQL to set the database in MULTI_USER mode.

USE master
GO
DECLARE @kill varchar(max) = '';
SELECT @kill = @kill + 'KILL ' + CONVERT(varchar(10), spid) + '; '
FROM master..sysprocesses 
WHERE spid > 50 AND dbid = DB_ID('<Your_DB_Name>')
EXEC(@kill);

GO
SET DEADLOCK_PRIORITY HIGH
ALTER DATABASE [<Your_DB_Name>] SET MULTI_USER WITH NO_WAIT
ALTER DATABASE [<Your_DB_Name>] SET MULTI_USER WITH ROLLBACK IMMEDIATE
GO
2016/08/30

To switch out of Single User mode, try:

ALTER DATABASE [my_db] SET MULTI_USER

To switch back to Single User mode, you can use:

ALTER DATABASE [my_db] SET SINGLE_USER

2013/09/23

  1. Right click your database in databases section
  2. Select "Properties"
  3. Select "Options" page
  4. Scroll down "Other options" and alter "Restrict access" field

screenshot of options page of sql server

2016/11/14

I tried this is working

ALTER DATABASE dbName SET MULTI_USER WITH ROLLBACK IMMEDIATE

I had the same problem, and the session_id to kill was found using this query:

Select request_session_id From sys.dm_tran_locks Where resource_database_id=DB_ID('BI_DB_Rep');
2016/08/22