SQL Server: How to Start SQL Server with Minimal Configuration

Introduction

There can be certain scenarios when SQL Server is not starting because of configuration problems. During such scenarios one need to start an instance of SQL Server by using minimal configuration. Using -f startup optionone can start SQL Server Instance with minimal configuration and this startup option will put the instance in a Single User Mode automatically.

In this article we will take a look at steps which one needs to follow to start SQL Server with Minimal Configuration to troubleshoot SQL Server configuration issues.

What happens when SQL Server is started in Minimal Configuration?

If you start an instance of SQL Server in Minimal Configuration then:-

• If you have configured any Startup Stored Procedures then it will not run during startup.
• At any given time only one user can connect to an instance of SQL Server.
• CHECKPOINT process will not be executed when the SQL Server Instance is running with Minimal Configuration.
• Remote access of SQL Server Instance is disabled.
• Read-Ahead is also disabled when an instance is configured to start with minimal configuration.

Related Articles

• Different Startup Options for SQL Server Database Engine Service
• How to Start SQL Server in Single User Mode
• How to Start SQL Server without TempDB Database
• How to Use Dedicated Administrator Connection (DAC) in SQL Server
• How to Move TempDB to a New Drive in SQL Server
• How to Repair Suspect Database in SQL Server
• Steps to Connect to SQL Server When all System Administrators are Locked Out

Once the server is started in Minimal Configuration a database administrator can change the appropriate server option using SP_CONFIFURE system stored procedure using SQLCMD or SSMS Query Window. Once you have made the necessary changes go ahead and stop and restart SQL Server.

How to Start SQL Server with Minimal Configuration or without TempDB database

1. Open Command Prompt as an administrator and then go to the BINN directory where SQL Server is installed and type sqlservr.exe /f /c. On our Production Server SQL Server is installed on the following drive location “E:\Program Files\Microsoft SQL Server\MSSQL10_50.SQL2014\MSSQL\Binn\“.

 sqlservr.exe /f /c

2. Open New Command Prompt window as an administrator and then Connect to SQL Server Instance Using SQLCMD.

SQLCMD –S localhost –E

3. Once you have performed the troubleshooting steps. Exit SQLCMD window by typing Quit and Press Enter. For more information. see, How to Start SQL Server without TempDB

4. In the initial window click CTRL C and enter Y to Stop SQL Server Service.

5. Finally, start SQL Server Database Engine Using SQL Server Configuration Manager.

Best Practices to follow when connecting to SQL Server Instance with Minimal Configuration

It is recommended to use SQLCMD command line utility and the Dedicated Administrator Connection (DAC) to connect to the SQL Server Instance. In case if you use normal connection then stop SQL Server Agent service as it will use the first available connection thereby blocking other users.

Conclusion

It is highly recommended to use SQLCMD command line utility to connect to SQL Server when you face configuration issues with SQL Server.