Starting SQL Server with minimum configuration

The option to start up Microsoft SQL Server when you have configuration problems is to use the -f start up option. With this option SQL Server start with a minimum configuration placing the server in single-user mode.

Be aware of the following in this mode:

• Only a single user can connect, and the CHECKPOINT process is not executed.
• Remote access and read-ahead are disabled.
• Startup stored procedures do not run.

Use the sqlcmd utility and the dedicated administrator connection to connect to SQL Server.

You can use the SC command line utility as you can use the start up parameters. SC is a command line program for communicating with the Service Control (SC) Manager and Services.

If you do not know how to use SC (Services Control Manager) review this post.

If you now start the SQL Server with a - f option the server with minimum configuration as shown:

I have a named instance of SQL Server 2016 called OHANA on my computer (Hodentek8) and I will now start it with minimum configuration by running the following. I have stopped this server before running the code:

 StartWithMin.png

 Verify with the Task Manager (there are other ways as well, Task Manager is open now).

StartWithMin_2

Starting and stopping a SQL Server using Services Control Manager

SC is a command line program for communicating with the Service Control (SC) Manager and Services. This can be used to start and stop a service including SQL Server.

Typing SC at command line (preferably with administrative privileges) will give you the details of this command.

SC_00

The syntax for using SC is:
        Service Control Manager and services.
USAGE:
        sc <server> [command] [service name] <option1> <option2>...

In this post we will just start and stop the SQL Server 2016 instance  and monitor it on the Task Manager.

The Service Name for the SQL Server Instance (OHANA) can be found by looking at the  details of SQL Server Instance (OHANA) in the Services window in Control Panel shown here:

SC_01

To start the SQL Server instance just type in the command

SC Start MSSQL$OHANA

SC_02

The Server has started. Bring up the Task Manager as shown and verify it has started. Note that the PID is the same.

SC_03

Now that the Server is running we can stop it by running the following command:

SC Stop MSSQL$OHANA

SC_04

Verify it has stopped in Task Manager.

SC_05

Starting and stopping using SC does not produce windows messages that you get while using Control Panel|..|Services. Using Task Manager you do not need to refresh window to see changes.

It is possible to use parameters to start a service as well.