Database Engine Service startup options
Applies to: SQL Server
Startup options designate certain file locations needed during startup, and specify some server wide conditions. Most users don't need to specify startup options unless you are troubleshooting the Database Engine or you have an unusual problem and are directed to use a startup option by SQL Server Customer Support.
Improper use of startup options can affect server performance and can prevent SQL Server 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]
About startup options
When you install SQL Server, Setup writes a set of default startup options in the Microsoft 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 Database Engine can't locate the necessary files, SQL Server won't start.
Startup options can be set by using SQL Server Configuration Manager. For information, see Configure Server Startup Options (SQL Server Configuration Manager).
The following screenshot shows the Startup Parameters tab in the SQL Server Properties dialog, where you can modify startup parameters.
List of startup options
Default startup options
|-d master_file_path||The fully qualified path for the
|-e error_log_path||The fully qualified path for the error log file (typically, C:\Program Files\Microsoft SQL Server\MSSQL.n\MSSQL\LOG\ERRORLOG). If you don't provide this option, the existing registry parameters are used.|
|-l master_log_path||The fully qualified path for the
Other startup options
|-c||Shortens startup time when starting SQL Server from the command prompt. Typically, the SQL Server Database Engine starts as a service by calling the Service Control Manager. Because the SQL Server Database Engine doesn't start as a service when starting from the command prompt, use -c to skip this step.|
|-f||Starts an instance of SQL Server 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 SQL Server in minimal configuration mode places SQL Server 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. That is, if you specify a very low value for the parameter, you may experience a longer recovery time and backups may take a slightly longer time to finish because a checkpoint process that a backup initiates is also delayed.
Instead of using this parameter, you use the following methods to help eliminate I/O bottlenecks on your system:
- Provide appropriate hardware to sustain I/O requests that are posted by SQL Server
- Perform sufficient application tuning
|-m||Starts an instance of SQL Server in single-user mode. When you start an instance of SQL Server in single-user mode, only a single user can connect, and the CHECKPOINT process isn't 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
|-mClient Application Name||Limits the connections to a specified client application. For example,
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:
Security note: Don't 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||Doesn't use the Windows application log to record SQL Server events. If you start an instance of SQL Server with -n, we recommend that you also use the -e startup option. Otherwise, SQL Server events aren't logged.|
|-s||Allows you to start a named instance of SQL Server. Without the -s parameter set, the default instance tries 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
|-T trace#||Indicates that an instance of SQL Server 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
|-x||Disables the following monitoring features:
- SQL Server 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 SQL Server 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 shouldn't be used in other applications because it might adversely affect performance. This option isn't supported in 32-bit releases of SQL Server.|
Use startup options for troubleshooting
Some startup options, such as single-user mode and minimal configuration mode, are principally used during troubleshooting. Starting the server for troubleshooting with the
-f options is easiest at the command line, while manually starting sqlservr.exe.
When SQL Server is started by using
net start, startup options use a slash (
/) instead of a hyphen (
Use startup options during normal operations
You may want to use some startup options every time you start SQL Server. These options, such as starting with a trace flag, are most easily done by configuring the startup parameters by using SQL Server Configuration Manager. This tool saves the startup options as registry keys, enabling SQL Server to always start with the startup options.
For options that have been removed from previous releases, see sqlservr Application.