Sql Server – TroubleShooting – Starting Minimally

Background

In some cases we have to dig a bit into SQL Server and see why things are not running in the manner we expected.

To get that kind of sit, we have to start the engine in so called minimal mode.

 

Starting Up

Identify SQL Server Path

Using Services applet, find “SQL Server” services, right click on the service and in the “General” Tab record the executable.

What we need is the path of that executable.

Console

Launch Command Shell

Launch Command shell ( cmd.exe ) in administrator mode.

Change Directory to Services BINN Folder

Change folder using cd /D to the identified path.

Stop

Please via command line, services applet, or SQL Server Configuration Manager stop the SQL Server Instance if it is current running.

 

Starting Up

Depending on whether it is the default or named instance we take a different tact.

Default Instance

Syntax
sqlservr.exe -f -sMSSQLSERVER
Sample

Here we start the default SQL Server Instance.

sqlservr.exe -f -sMSSQLSERVER

Named Instance

Named Instance – CHASE

Syntax
sqlservr.exe -f -s [instance-name]
Sample

Here we start a named SQL Server Instance, CHASE.

sqlservr.exe -s CHASE -f

 

Output
Image

Textual
  1. Server process ID is 336.
    • Task Manager :- Process ID
      • PID :- 336
  2. Server System Manufacturer: ‘VMware, Inc.’, System Model: ‘VMware Virtual Platform‘.
    • Server System Manufacturer
      • ‘VMware, Inc.’, System Model: ‘VMware Virtual Platform’.
  3. Server Logging SQL Server messages in file ‘C:\Progr
    am Files\Microsoft SQL Server\MSSQL13.CHASE\MSSQL\Log\ERRORLOG‘.

    • Log File
      • C:\Progr
        am Files\Microsoft SQL Server\MSSQL13.CHASE\MSSQL\Log\ERRORLOG’
  4. Warning: The server instance was started using minimal configuration startup option (-f). Starting an instance of SQL Server with minimal configuration places the server in single-user mode automatically.
    After the server has been started with minimal configuration, you should change
    the appropriate server option value or values, stop, and then restart the server.

    • Startup Option
      • -f
        • Single-user mode
        • Change appropriate server option value or values
        • Restart Server
  5. Server is listening on [ ‘any’ <ipv6> 50000]
    Server is listening on [ ‘any’ <ipv4> 50000]
    Server is listening on [ ‘any’ <ipv6> 53275]
    Server is listening on [ ‘any’ <ipv4> 53275]

    • Network Ports of IVP6 and IPV4
    • Network Port # 50000 and 53275

Summary

Accessing the SQL Server Engine through the command line is a time tested, battle hardened pathway that we will return to in later posts.

Because it is rarely needed and because of the harm it can cause, will only revisit when can be done responsibly in a public space.

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s