SQL Server Articles, SQL Server Tips, SQL Server Tutorials, SQL Server Tuning, SQL Server DBA, SQL Server Basics, Training, etc - MyTechMantra.com

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.

Ashish Mehta

Ashish Mehta

Ashish Kumar Mehta is a database manager, trainer and technical author. He has more than a decade of IT experience in database administration, performance tuning, database development and technical training on Microsoft SQL Server from SQL Server 2000 to SQL Server 2014. Ashish has authored more than 325 technical articles on SQL Server across leading SQL Server technology portals. Over the last few years, he has also developed and delivered many successful projects in database infrastructure; data warehouse and business intelligence; database migration; and upgrade projects for companies such as Hewlett-Packard, Microsoft, Cognizant and Centrica PLC, UK. He holds an engineering degree in computer science and industry standard certifications from Microsoft including MCITP Database Administrator 2005/2008, MCDBA SQL Server 2000 and MCTS .NET Framework 2.0 Web Applications.

Newsletter Signup! Join 15,000+ Professionals




Be Social! Like & Follow Us...

Follow us

Don't be shy, get in touch. We love meeting interesting people and making new friends.

Recent SQL Server Tips

Manning