Forum Discussion
Unable to change Single User Mode to Multi User Mode in SSMS v18.12.1
Hi all,
Appreciate it if someone can shed some light on this issue. I need some assistance on how to change a database back to multi-user mode from single user mode.
Here is a complete scenario of why the database was set in Single-Mode in the first place.
1. Initially I wanted to use a database that I restored in NAV 2017 Development Environment CU61 (Cumulative Update) in let say Server A, but it failed and asked the database to be converted first before using. I suspect it was due to the database was originally backup and restored from a database that used to open in older CU for NAV 2017.
2. Usually from what I have seen, it will prompt a message to tell the user like "The database will be converted before using, are you sure you want to proceed" or something like that. If this message is prompted, I would have let it converted automatically by the system and proceed to use the database.
3. For my case, its different, it prompt another type of message saying that "The database cannot be converted because a user is using it/connected to it" or something similar.
4. Upon checking via the "Activity Monitor" on my SQL server, let say server B, I was the only user connecting to this database and using the process. I tried to manually kill the process but to no avail.
5. I found some article stating to try and change the mode from Multi-User mode to Single User Mode. For testing purpose, I tried it out, once I figured out its not working, I am planning to switch it back, but it won't let me switch it back to multi user mode. It keeps stating that a user is connected to it.
6. I tried deleting the database and restore it again, but it won't let me delete the database either.
That is basically the gist of my issue. I am hoping to get some guidance and solution on this. Thank you and have a nice day.
- Hello
You can try below
Use master
Go
ALTER DATABASE YOURDATABASE SET MULTI_USER WITH ROLLBACK IMMEDIATE
GO
Regards
JavierHi 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
