Forum Discussion
Unable to change Single User Mode to Multi User Mode in SSMS v18.12.1
You can try below
Use master
Go
ALTER DATABASE YOURDATABASE SET MULTI_USER WITH ROLLBACK IMMEDIATE
GO
Regards
Javier
Hi Javier,
Thank you so much for the suggestion, much appreciated. However, I wanted to clarify something if you don't mind. Based on the SQL script that you have suggested, is it a must to use "master" instead of my actual database name? I am worried that if I used the "master" database, it will affect all the database that are in the SSMS as well. Will it cause that kind of issue?
For you information, I tried using my actual database name and I got the error as shown below.
Msg 924, Level 14, State 1, Line 1
Database '[ACTUAL_DATABASE_NAME] is already open and can only have one user at a time.
Msg 1205, Level 13, State 68, Line 3
Transaction (Process ID 74) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
Msg 5069, Level 16, State 1, Line 3
ALTER DATABASE statement failed.
Can I also know the role of this "master" if possible, I am still new to SQL, SSMS and so on. Much thanks and have a good day.
Hi, Desmond.
Yes, using the [master] database is totally fine - even expected in some cases.
Technically, you can run Javier's command from any database as it's not targeting the currently-scoped database anyway.
The database you're currently scoped to does not prevent you from running commands against other databases, so being focused on [master] is not an issue at all.
Cheers,
Lain
- Hi Lain,
Thank you for the heads up. For the SQL script that Javier has recommended, do I run it on the database whereby I right click on the specific database that I am having the issue and select "New Query" or something else?Hi, Desmond.
You can right-click on any database (that you can access, of course) and run it, or simply just right-click the root node at the top of the Object Explorer:
It doesn't matter, just so long as you're on the correct server.
The very first command of "USE [master]" will switch focus from whatever database you were on to the [master] database anyway, which is why it doesn't matter.
Cheers,
Lain
