SQL Server: How to Start SQL Server in Single User Mode?

Introduction

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 Managerbefore 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.

Related Articles

• 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.

Reference

• Start SQL Server in Single-User Mode