There are some weird situations which I see in SQL Server. This is also a one of the surprising situation. One of my clients reported via below screen shot. Let us see the issue of Master Database in Single User Mode.
Interesting enough, there was no issue with SQL functionality. It was acting just normally which means that we were able to make connections to SQL Server without any issues. They said that there are no users complains and users were able to get multiple connections to the Master as well.
We tried to switch it back to multi_user both in T-SQL and GUI, we get the same error:
Msg 5058, Level 16, State 5, Line 1
Option ‘MULTI_USER’ cannot be set in database ‘master’.
Since, it was interesting, I asked to check sp_helpdb for master and check the same in sys.database catalog views.
Both of the above have conflicting information. While this is an unexpected behavior, how do we come out of this situation?
Here is the solution I offered them.
- Take the backup of master database using the backup database command.
- Restored the backup taken in the previous step as a user database. Let’s say master_one.
- The use ALTER command and get master_one in multiuser mode.
- Now take the backup of that user database (master_one) and then restored it in master using the below command. To restore the master database, we need to start SQL in single user mode.
Restore database master from disk= 'C:\Temp\Master_one.bak' with replace
Once they followed the above steps, the master database was back to multi user mode. My client was not sure how they ended up in such situation.
Reference: Pinal Dave (http://blog.SQLAuthority.com)
For a SQL 2016 instance, a DB creation date of 2003 looks odd. In order to preserve the system information, maybe they tried to replace the physical files with those from a SQL 2000 server and hence, the database engine got confused (it tried to put the DB in single-user for recovery, but failed somewhere in the process).
I believe master has been carried forward by Microsoft since those days. I am not surprised with the date as master is never created during installation as files are reused for the media.
Does “extended events” help in troubleshooting the “cause” of the issue before performing the work around suggested ?
I didn’t get chance to research on that.