Forum Discussion
Unable to change Single User Mode to Multi User Mode in SSMS v18.12.1
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
- Hello,
You have to replace YOURDATABASE by the database name that you have the issue with. And the script is fine to go to master since you cannot run the script if you are in the content of the DB you need to change so it is OK to run
Regards
Javier
Use master
Go
ALTER DATABASE YOURDATABASE SET MULTI_USER WITH ROLLBACK IMMEDIATE
GO
