SQL Server: How to Start SQL Server in Single User Mode?
Related Topics: SQL Server Disaster Recovery Tips>
Next Topic: SQL Server Indexing Articles & Tips
There can be certain scenarios when one needs to connect to an SQL Server
Instance in a Single User Mode by using the
Startup Option -m. For example, the need could be to
recover a damaged system database such as Master, Model, MSDB etc or you may
want to change the server configuration options.
In this article we will take a look at steps which one needs to follow to Start SQL Server in Single User Mode.
Advantages of Starting SQL Server in Single User Mode
The advantage of starting SQL Server in single-user mode is that it will enable any member of the server's Local Administrators Group to connect to the instance of SQL Server as a member of SYSADMIN (SA) fixed server role. For more information, we recommend reading Steps to Connect to SQL Server When System Administrators Are Locked Out.
Common Issues Encountered by DBAs when they start SQL Server in Single User Mode
As a Best Practice, stop the SQL Server Agent service from SQL Server Configuration Manager before connecting to an instance of SQL Server in single-user mode; otherwise, you will be blocked as SQL Server Agent service will use the only available connection.
How to Manage SQL Server in Single User Mode
When SQL Server is in Single User Mode a DBA should execute TSQL commands either by using SQLCMD or by using Query Editor of SQL Server Management Studio. For detailed information on supported SQL Server Startup option read Different Startup Options for SQL Server Database Engine Service.
Read the following step by step guide to learn How to Connect to SQL Server When System Administrators Are Locked Out.
• What are Virtual Log Files in SQL Server Transaction Log File?
• How to Use Dedicated Administrator Connection in SQL Server
• How to Start SQL Server without TempDB Database?
• Different Startup Options for SQL Server Database Engine Service
• How to Detect Corruption Issues in SQL Server Using Suspect_Pages Table?
• How to Fix SQL Server Database Corruption Issues?
• Steps to Repair a Suspect Database in SQL Server?
• Different Ways to Find Default Trace Location in SQL Server
How to Connect to SQL Server in Single User Mode in a Clustered Installation
In clustered environment when SQL Server is started in single user mode, the cluster resource DLL uses up the available single connection thereby preventing any other connection to SQL Server. Follow the below steps to resolve this issue.
1. From SQL Server Advanced Properties remove –m startup parameter
2. Using Failover Cluster Manager, take the SQL Server Resource Offline
3. Identify the current owner of Cluster Group and run the following command from the command prompt:
Net Start MSSQLSERVER /m
4. Using Failover Cluster Manager verify that the SQL Server Resource is still Offline
5. Using Command Prompt connect to SQL Server instance using
SQLCMD and then
execute the following
command to connect to the instance.
SQLCMD -E -S <servername>
6. Once you have completed the activities close the command prompt and then bring the SQL Server and
other resources online using Failover Cluster Manager.
Thank you for taking your time to read
this article. Let's be Connected....
More... Disaster Recovery Tips for DBAs and Developers
- Different Ways to Retrieve SQL Server Configuration Details
- How to Move TempDB Database Files to a New Drive in SQL Server
- Improved Startup Parameters in SQL Server 2012
- Steps to Connect to SQL Server When all System Administrators are Locked Out
- How to Repair Suspect Database in SQL Server
- How to Connect to a Named Instance of SQL Server
- Introduction to SQL Server Configuration Manager
- SQL Server Disaster Recovery Tips for DBAs and Developers
- How to Perform PARTIAL Backup in SQL Server a Step by Step Tutorial with Examples
Last Updated On: May 05, 2016
Please leave your Valuable Comment or Let us know how this article helped you: