Unable to change Single User Mode to Multi User Mode in SSMS v18.12.1

desmondfmw99 

Try running the following to see which login - and from where - has the lock.

If you don't have permissions, you'll need to obtain them or have someone else that does run this content for you.

SELECT
	[s].[session_id]
	, [d].[name] AS [database]
	, [s].[login_name]
	, [s].[host_process_id]
	, [s].[program_name]
	, [s].[host_name] AS [remote client]
	, [c].[net_transport]
	, [c].[client_net_address] AS [remote client address]
FROM
	[master].[sys].[dm_exec_sessions] AS [s] INNER JOIN
	[master].[sys].[databases] AS [d] ON
		[s].[database_id] = [d].[database_id] INNER JOIN
	[master].[sys].[dm_exec_connections] AS [c] ON
		[s].[session_id] = [c].[session_id]
WHERE
	[d].[name] = 'yourDatabaseNameGoesHere'
ORDER BY
	[d].[name]
	, [s].[login_name]
	, [remote client];

If the results return a single row - which should be the case, you can then try the following (i.e. don't bother if there's more than one row).

Example output

Depending on the nature of what you see, possible next actions include:

• Stop the remote application/process so it releases the lock on the database;
• Use the KILL statement to terminate that session (rarely works as a standalone solution);
• Add KILL to the ALTER DATABASE statement to improve your chances of bringing the database back into MULTI_USER mode before the remote process can re-obtain another lock.

• KILL (Transact-SQL) - SQL Server | Microsoft Learn

Note: KILL has some forceful options which I wouldn't suggest using until you've tried it without those options.

I'm a fan of the first option as a priority but it's not always possible.

I'd try the third option next only if the first option is a non-starter, which would look like this (you have to note down the session_id from the result of the previous query):

KILL 76		-- This should be your session_id from the earlier query. 76 is only an example value from my earlier output above.
GO

-- Now try setting the database back to MULTI_USER mode.
ALTER DATABASE
	[yourDatabaseNameGoesHere]
SET
	MULTI_USER;

Cheers,

Lain