MyTechMantra.com
Connect With MyTechMantra.com

Like Us on Facebook    Follow Us on Twitter     Subscribe to our Feeds     Subscribe to NewsLetter





































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

Read Comments   |   Related Topics: SQL Server Disaster Recovery Tips > Next Topic: SQL Server Indexing Articles & Tips


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

Related Tips

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

Sign-up for Our Newsletter to Get Free SQL Server Tips and News to Build your Career

Like MyTechMantra on Facebook to get updates on What's Happening in SQL Server

Reference:

More... Disaster Recovery Tips for DBAs and Developers







Last Updated On: May 05, 2016



Share this Article



Receive Free SQL Server Tips and Keep Learning
Get Free SQL Server Tips




Please leave your Valuable Comment or Let us know how this article helped you: