Problem
Last week we had a strange issue on one of our SQL Server instances hosted in our QA environment. The instance was starting in single user mode every time despite applying fixes. Here I will explain how to fix such issue in a step by step approach.
Solution
In SQL Server, single user mode means only one user can connect to the SQL Server instance. When the SQL Server instance is started in single-user mode, it will not allow others to establish a connection to the server at the same time. For example, if you’ve already connected to your instance in single user mode then nobody else can make a connection to that instance and you will receive the below error message while trying to make the connection:
administrator can connect at this time. (Microsoft SQL Server, Error: 18461)
Checking SQL Server Startup Parameters
The first thing that comes to a DBA’s mind after getting this issue is to check the startup parameters to see if the -m parameter is being used. The -m parameter starts up SQL Server in single user mode.
I checked the startup parameters and did not find the -m startup parameter that can cause the instance to start in single user mode.
You can access SQL Server startup parameters as follows:
- Launch SQL Server Configuration Manager
- Select the SQL Server service and right click and select Properties
- Click on the Advanced tab and look at Startup Parameters
Checking the SQL Server Error Log
Another strange observation was made when we looked at the SQL Server error log where it was clearly seen that SQL Server database engine was starting in single user mode with the -m parameter as shown below.
We tried restarting SQL Server to see if we could get SQL Server start in multiuser mode, but nothing helped and every time we restarted the SQL Server service, it started in single user mode and we could see the -m in error log file.
We also checked instance level settings to see if the maximum number of concurrent users was set to 1. Although, it’s not recommended to set this value to 1 in any case because this can prevent even an administrator to login, but we checked this setting to rule out we are not missing any possible cause. To access this setting, you can go to instance level Properties page, then click Connections, and on the right side pane you will see the setting “Maximum number of concurrent users”. You can leave this value with default value.
We also tried logging using the dedicated administrator connection, but this approach was not successful as well.
Checking Registry Keys
As we were running out of options to fix this issue, I thought about looking at all the registry entries for SQL Server. Thanks to Microsoft a DMV has been provided to gather all registry entries related to SQL Server in one output file, the DMV is sys.dm_server_registry. This DMV returns configuration and installation information that is stored in the Windows registry for the current instance of SQL Server. It returns one row per registry key.
Run the below command to get this information.
--Gather all SQL Server related registry information SELECT * FROM sys.dm_server_registry
You can see the output of the above query in the below image.
You can review each of these keys, but we focused in on the ImagePath line. We could see the -m parameter is mentioned in the value of SQL Server image path. It means, every time SQL Server starts, it will use -m parameter and that is what was happening on our instance.
Next, we wanted to validate this entry directly from the registry. We launched the Windows Registry and went to the registry key location that is shown above. You can see below that the same information is shown in the registry.
The next step is to edit the registry key and remove the -m from the registry entry to make SQL Server available for multiuser access.
Note: You should always make a registry backup before making registry changes. Also, it is not advisable to make registry changes unless you are sure the change will not cause other problems.
Restart SQL Server
Once we removed -m from above registry entry we restarted the SQL Server service.
This time SQL Server started in multiuser mode and all users were able to make connections to the database instance.
We also verified this by looking at the SQL Server error log file. You can see the -m parameter was not there after restarting the instance.
Another way to validate this is to check the registry entries again as compared to the DMV sys.dm_server_registry.
Summary
It’s still not clear how the -m parameter was added to the registry. The SQL Server instance was running fine, but after we had to do a restart, the SQL Server started in single user mode.
It is possible this was changed in one of the GUI tools and the registry didn’t update correctly or someone changed the registry directly. Please share your views and possible cause of this issue if you have also faced a similar issue.
Next Steps
- Be careful when making changes directly to the registry and always create a backup of the registry.
- Read more articles on SQL Server Database Administration
