| title | Database Engine Service Startup Options | Microsoft Docs | ||||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| ms.custom | |||||||||||||||
| ms.date | 05/31/2019 | ||||||||||||||
| ms.prod | sql | ||||||||||||||
| ms.prod_service | high-availability | ||||||||||||||
| ms.reviewer | |||||||||||||||
| ms.technology | configuration | ||||||||||||||
| ms.topic | conceptual | ||||||||||||||
| helpviewer_keywords |
|
||||||||||||||
| ms.assetid | d373298b-f6cf-458a-849d-7083ecb54ef5 | ||||||||||||||
| author | MikeRayMSFT | ||||||||||||||
| ms.author | mikeray |
[!INCLUDEappliesto-ss-xxxx-xxxx-xxx-md]
Startup options designate certain file locations needed during startup, and specify some server wide conditions. Most users do not need to specify startup options unless you are troubleshooting the [!INCLUDEssDE] or you have an unusual problem and are directed to use a startup option by [!INCLUDEssNoVersion] Customer Support.
Warning
Improper use of startup options can affect server performance and can prevent [!INCLUDEssNoVersion] from starting.
Start SQL Server on Linux with the "mssql" user to prevent future startup issues. Example: sudo -u mssql /opt/mssql/bin/sqlservr [STARTUP OPTIONS]
When you install [!INCLUDEssNoVersion], Setup writes a set of default startup options in the [!INCLUDEmsCoName] Windows registry. You can use these startup options to specify an alternate master database file, master database log file, or error log file. If the [!INCLUDEssDE] cannot locate the necessary files, [!INCLUDEssNoVersion] will not start.
Startup options can be set by using [!INCLUDEssNoVersion] Configuration Manager. For information, see Configure Server Startup Options (SQL Server Configuration Manager).
| Options | Description |
|---|---|
| -d master_file_path | Is the fully qualified path for the master database file (typically, C:\Program Files\Microsoft SQL Server\MSSQL.n\MSSQL\Data\master.mdf). If you do not provide this option, the existing registry parameters are used. |
| -e error_log_path | Is the fully qualified path for the error log file (typically, C:\Program Files\Microsoft SQL Server\MSSQL.n\MSSQL\LOG\ERRORLOG). If you do not provide this option, the existing registry parameters are used. |
| -l master_log_path | Is the fully qualified path for the master database log file (typically C:\Program Files\Microsoft SQL Server\MSSQL.n\MSSQL\Data\mastlog.ldf). If you do not specify this option, the existing registry parameters are used. |
| Options | Description |
|---|---|
| -c | Shortens startup time when starting [!INCLUDEssNoVersion] from the command prompt. Typically, the [!INCLUDEssDEnoversion] starts as a service by calling the Service Control Manager. Because the [!INCLUDEssDEnoversion] does not start as a service when starting from the command prompt, use -c to skip this step. |
| -f | Starts an instance of [!INCLUDEssNoVersion] with minimal configuration. This is useful if the setting of a configuration value (for example, over-committing memory) has prevented the server from starting. Starting [!INCLUDEssNoVersion] in minimal configuration mode places [!INCLUDEssNoVersion] in single-user mode. For more information, see the description for -m that follows. |
| -kDecimalNumber | This startup parameter limits the number of checkpoint I/O requests per second, where the DecimalNumber represents the checkpoint speed in MB per second. Changing this value can impact the speed of taking backups, or going through the recovery process so proceed with caution. For more information about this startup parameter, please see hot fix where the -k parameter was introduced. |
| -m | Starts an instance of [!INCLUDEssNoVersion] in single-user mode. When you start an instance of [!INCLUDEssNoVersion] in single-user mode, only a single user can connect, and the CHECKPOINT process is not started. CHECKPOINT guarantees that completed transactions are regularly written from the disk cache to the database device. (Typically, this option is used if you experience problems with system databases that should be repaired.) Enables the sp_configure allow updates option. By default, allow updates is disabled. Starting [!INCLUDEssNoVersion] in single-user mode enables any member of the computer's local Administrators group to connect to the instance of [!INCLUDEssNoVersion] as a member of the sysadmin fixed server role. For more information, see Connect to SQL Server When System Administrators Are Locked Out. For more information about single-user mode, see Start SQL Server in Single-User Mode. |
| -mClient Application Name | Limits the connections to a specified client application. For example, -mSQLCMD limits connections to a single connection and that connection must identify itself as the SQLCMD client program. Use this option when you are starting [!INCLUDEssNoVersion] in single-user mode and an unknown client application is taking the only available connection. Use "Microsoft SQL Server Management Studio - Query" to connect with the SSMS Query Editor. The SSMS Query Editor option cannot be configured by using [!INCLUDEssNoVersion_md] Configuration Manager because it includes the dash character which is rejected by the tool.Client Application Name is case sensitive. Double quotes are required if the application name contains spaces or special characters. Examples when starting from the command line: C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Binn\sqlservr -s MSSQLSERVER -m"Microsoft SQL Server Management Studio - Query" C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Binn\sqlservr -s MSSQLSERVER -mSQLCMD Security Note: Do not use this option as a security feature. The client application provides the client application name, and can provide a false name as part of the connection string. |
| -n | Does not use the Windows application log to record [!INCLUDEssNoVersion] events. If you start an instance of [!INCLUDEssNoVersion] with -n, we recommend that you also use the -e startup option. Otherwise, [!INCLUDEssNoVersion] events are not logged. |
| -s | Allows you to start a named instance of [!INCLUDEssNoVersion]. Without the -s parameter set, the default instance will try to start. You must switch to the appropriate BINN directory for the instance at a command prompt before starting sqlservr.exe. For example, if Instance1 were to use \mssql$Instance1 for its binaries, the user must be in the \mssql$Instance1\binn directory to start sqlservr.exe -s instance1. |
| -T trace# | Indicates that an instance of [!INCLUDEssNoVersion] should be started with a specified trace flag (trace#) in effect. Trace flags are used to start the server with nonstandard behavior. For more information, see Trace Flags (Transact-SQL). Important: When specifying a trace flag with the -T option, use an uppercase "T" to pass the trace flag number. A lowercase "t" is accepted by [!INCLUDEssNoVersion], but this sets other internal trace flags that are required only by [!INCLUDEssNoVersion] support engineers. (Parameters specified in the Control Panel startup window are not read.) |
| -x | Disables the following monitoring features: - [!INCLUDEssNoVersion] performance monitor counters - Keeping CPU time and cache-hit ratio statistics - Collecting information for the DBCC SQLPERF command - Collecting information for some dynamic management views - Many extended-events event points Warning: When you use the -x startup option, the information that is available for you to diagnose performance and functional problems with [!INCLUDEssNoVersion] is greatly reduced. |
| -E | Increases the number of extents that are allocated for each file in a filegroup. This option may be helpful for data warehouse applications that have a limited number of users running index or data scans. It should not be used in other applications because it might adversely affect performance. This option is not supported in 32-bit releases of [!INCLUDEssNoVersion]. |
Some startup options, such as single-user mode and minimal configuration mode, are principally used during troubleshooting. Starting the server for troubleshooting with the -m or -f options is easiest at the command line, while manually starting sqlservr.exe.
Note
When [!INCLUDEssNoVersion] is started by using net start, startup options use a slash (/) instead of a hyphen (-).
You may want to use some startup options every time you start [!INCLUDEssNoVersion]. These options, such as starting with a trace flag, are most easily done by configuring the startup parameters by using [!INCLUDEssNoVersion] Configuration Manager. These tool saves the startup options as registry keys, enabling [!INCLUDEssNoVersion] to always start with the startup options.
The -h parameter is not supported in [!INCLUDEssCurrent]. This parameter was used in earlier versions of 32-bit instances of [!INCLUDEssNoVersion] to reserve virtual memory address space for Hot Add memory metadata when AWE is enabled. For more information, see Discontinued SQL Server Features in SQL Server 2016.
Configure the scan for startup procs Server Configuration Option
Start, Stop, Pause, Resume, Restart the Database Engine, SQL Server Agent, or SQL Server Browser Service
Configure Server Startup Options (SQL Server Configuration Manager)