[sql-server] 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.

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');

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

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

Adding to Jespers answer, to be even more effective:

SET DEADLOCK_PRIORITY 10;-- Be the top dog.

SET DEADLOCK_PRIORITY HIGH uses DEADLOCK_PRIORITY of 5.

What is happening is that the other processes get a crack at the database and, if your process has a lower DEADLOCK_PRIORITY, then it loses the race.

This obviates finding and killing the other spid (which might need to be done several times).

It is possible that you would need to run ALTER DATABASE more than once, (but Jesper does that). Modified code:

USE [master]
SET DEADLOCK_PRIORITY HIGH
exec sp_dboption '[StuckDB]', 'single user', 'FALSE';
ALTER DATABASE [StuckDB] SET MULTI_USER WITH NO_WAIT
ALTER DATABASE [StuckDB] SET MULTI_USER WITH ROLLBACK IMMEDIATE

We just experienced this in SQL 2012. A replication process jumped in when we killed the original session that set it to single user. But sp_who2 did not show that new process attached to the DB. Closing SSMS and re-opening then allowed us to see this process on the database and then we could kill it and switch to multi_user mode immediately and that worked.

I can't work out the logic behind this, but it does appear to be a bug in SSMS and is still manifesting itself in SQL 2012.

Even I come across same problem, not able to find active connections to my_db to kill it but still shows same error. I end up disconnecting all possible SSMS connections for any database on the Server, create a new connection from SSMS and change it to Multi user.

-- Actual Code to change my_db to multi user mode
USE MASTER;
GO
ALTER DATABASE [my_db] SET MULTI_USER

Note: This seems to be a possible bug in SQL Server 2005!

use master

GO

select d.name, d.dbid, spid, login_time, nt_domain, nt_username, loginame from sysprocesses p inner join sysdatabases d on p.dbid = d.dbid where d.name = 'database name'

kill 568 -- kill spid

ALTER DATABASE database name'

SET MULTI_USER go

Not sure if this helps anyone, but I had the same issue and could not find the process that was holding me up. I closed SSMS and stopped all the services hitting the local instance. Then once I went back in and ran the exec sp_who2, it showed me the culprit. I killed the process and was able to get the Multi_User to work, then restart the services. We had IIS hitting it every few minutes/seconds looking for certain packages.

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

The following worked for me:

USE [master]
SET DEADLOCK_PRIORITY HIGH
exec sp_dboption '[StuckDB]', 'single user', 'FALSE';
ALTER DATABASE [StuckDB] SET MULTI_USER WITH NO_WAIT
ALTER DATABASE [StuckDB] SET MULTI_USER WITH ROLLBACK IMMEDIATE

I tried this is working

ALTER DATABASE dbName SET MULTI_USER WITH ROLLBACK IMMEDIATE

Use this Script

exec sp_who

Find the dbname and spid column

now execute

kill spid 
go
ALTER DATABASE [DBName]
SET MULTI_USER;

I ran across the same issue this morning. It turned out to be a simple issue. I had a query window open that was set to the single user database in the object explorer. The sp_who2 stored procedure did not show then connection. Once I closed it, I was able to set it to

Just in case if someone stumbles onto this thread then here is a bullet proof solution to SQL Server stuck in SINGLE USER MODE

-- Get the process ID (spid) of the connection you need to kill
-- Replace 'DBName' with the actual name of the DB

SELECT sd.[name], sp.spid, sp.login_time, sp.loginame 
FROM sysprocesses sp 
INNER JOIN sysdatabases sd on sp.dbid = sd.dbid  
WHERE sd.[name] = 'DBName'

As an alternative, you can also use the command “sp_who” to get the “spid” of the open connection:

-- Or use this SP instead

exec sp_who

-- Then Execute the following and replace the [spid] and [DBName] with correct values

KILL SpidToKillGoesHere
GO

SET DEADLOCK_PRIORITY HIGH
GO

ALTER DATABASE [DBName] SET MULTI_USER WITH ROLLBACK IMMEDIATE
GO

Another option is to:

• take the database offline; in SMSS, right click database and choose Take Offline, tick 'Drop all connections'
• run ALTER DATABASE [Your_Db] SET MULTI_USER

Today I faced the same issue where my database was changed from Multi User to Single User mode and this was eventually stopping me to publish database.

In order to fix this issue, I had to close all Visual Studio instances and run the below command in Sql Server query window -

USE [Your_Database_Name]; ALTER DATABASE [Your_Database_Name] SET MULTI_USER GO

This command has changed the DB from Single user to Multi User and afterwards, I was successfully able to publish.

Press CTRL + 1

find the process that locks your database. Look in column dbname for your db and note the spid. Now you have to execute that statement:

kill <your spid>
ALTER DATABASE <your db> SET MULTI_USER;