Recently during the Comprehensive Database Performance Health Check, I had a very interesting situation when I had to set the database into single-user mode. However, while we were running the following statement we got an error. Let us see a quick workaround in this blog post about an error related to Database Giving Error While Setting Single_User Mode.
Let us see the command when we ran it start giving error to us.
ALTER DATABASE MyDb SET SINGLE_USER WITH ROLLBACk IMMEDIATE;
Whenever we tried the script above it gave us an error. If you want to take your database to single-user mode and getting errors they run the above script they can easily take the database into emergency mode first and run the command listed above.
ALTER DATABASE MyDb SET EMERGENCY
Once you take the database into emergency mode, you should be able to take the database into single-user mode.
Now, at this moment I must give you a couple of warnings. It is not necessary that once you take the database into single-user mode, you may not be able to get it to multi-user if there are serious errors. Additionally, any of the suggestions you see in this blog post, may not fix your problem, and if you are not sure you should reach out to a local expert or me on LinkedIn.
Let me know if you have ever faced this error and was able to solve the error with the solution listed here. This will help us to know more effectiveness of this suggestion.
Here are a few blog posts which you may find helpful:
- SQL SERVER – sp_who2 Parameters
- SQL SERVER – Fill Factor – Instance Level or Index Level
- SQL SERVER – sp_who2 Parameters
- SQL SERVER – Disable Rowgoal Optimizer
- SQL SERVER – Number of Rows Read – Execution Plan
- SQL SERVER – DBCC DBREINDEX and MAXDOP Not Possible
- SQL SERVER – Attach an In-Memory Database with T-SQL
Reference:Â Pinal Dave (https://blog.sqlauthority.com)
