MyTechMantra.com
Connect With MyTechMantra.com

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












































Latest SQL Server Tips

SQL Server: How to Start SQL Server with Minimal Configuration

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


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 option one 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 Tips

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.

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 06, 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: