Exit single-user mode

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;

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`

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][1]][1]


[1]:

Adding to [Jespers answer][1], 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


[1]:

[To see links please register here]

Use this Script

exec sp_who

Find the dbname and spid column

now execute

kill spid
go
ALTER DATABASE [DBName]
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.

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

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

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`.
3. 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][1] on managing database files. This was written for moving files, but user management is the same.


[1]:

[To see links please register here]