Configure Server Startup Options (SQL Server Configuration Manager)

 

Updated: January 6, 2016

Applies To: SQL Server 2016

This topic describes how to to configure startup options that will be used every time the Database Engine starts in SQL Server 2016 by using SQL Server Configuration Manager. For a list of startup options, see Database Engine Service Startup Options.

Limitations and Restrictions

SQL Server Configuration Manager writes startup parameters to the registry. They take effect upon the next startup of the Database Engine.

On a cluster, changes must be made on the active server when SQL Server is online, and will take effect when the Database Engine is restarted. The registry update of the startup options on the other node will occur upon the next failover.

Security

Permissions

Configuring server startup options is restricted to users who can change the related entries in the registry. This includes the following users.

  • Members of the local administrators group.

  • The domain account that is used by SQL Server, if the Database Engine is configured to run under a domain account.

To configure startup options

  1. Click the Start button, point to All Programs, point to Microsoft SQL Server 2016, point to Configuration Tools, and then click SQL Server Configuration Manager.

    System_CAPS_ICON_note.jpg Note


    Because SQL Server Configuration Manager is a snap-in for the Microsoft Management Console program and not a stand-alone program, SQL Server Configuration Manager does not appear as an application in newer versions of Windows.

    • Windows 10:
      To open SQL Server Configuration Manager, on the Start Page, type SQLServerManager13.msc (for SQL Server 2016). For previous versions of SQL Server replace 13 with a smaller number. Clicking SQLServerManager13.msc opens the Configuration Manager. To pin the Configuration Manager to the Start Page or Task Bar, right-click SQLServerManager13.msc, and then click Open file location. In the Windows File Explorer, right-click SQLServerManager13.msc, and then click Pin to Start or Pin to taskbar.
    • Windows 8:
      To open SQL Server Configuration Manager, in the Search charm, under Apps, type SQLServerManager<version>.msc such as SQLServerManager13.msc, and then press Enter.
  2. In SQL Server Configuration Manager, click SQL Server Services.

  3. In the right pane, right-click SQL Server (<instance_name>), and then click Properties.

  4. On the Startup Parameters tab, in the Specify a startup parameter box, type the parameter, and then click Add.

    For example, to start in single-user mode, type -m in the Specify a startup parameter box and then click Add. (When you restart SQL Server in single-user mode, stop the SQL Server Agent. Otherwise, SQL Server Agent might connect first and prevent you from connecting as a second user.)

  5. Click OK.

  6. Restart the Database Engine.

    System_CAPS_ICON_warning.jpg Warning


    After you are finished using single-user mode, in the Startup Parameters box, select the -m parameter in the Existing Parameters box, and then click Remove. Restart the Database Engine to restore SQL Server to the typical multi-user mode.

Start SQL Server in Single-User Mode
Connect to SQL Server When System Administrators Are Locked Out
Start, Stop, or Pause the SQL Server Agent Service

Community Additions

ADD
Show: