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.
Startup options can be set by using SQL Server Configuration Manager. For information, see How to: Configure Server Startup Options (SQL Server Configuration Manager).
You can override the default startup options
temporarily and start an instance of SQL Server by using the following
additional startup options.
Startup options can be set by using SQL Server Configuration Manager. For information, see How to: Configure Server Startup Options (SQL Server Configuration Manager).
| Default startup options | Description |
|---|---|
| -d master_file_path | 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 | 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 | 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. |
| Other startup options | Description |
|---|---|
| -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 does not 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. |
| -g memory_to_reserve | Specifies
an integer number of megabytes (MB) of memory that SQL Server will
leave available for memory allocations within the SQL Server process,
but outside the SQL Server memory pool. The memory outside of the memory
pool is the area used by SQL Server for loading items such as extended
procedure .dll files, the OLE DB providers referenced by distributed
queries, and automation objects referenced in Transact-SQL statements.
The default is 256 MB. Use of this option might help tune memory allocation, but only when physical memory exceeds the configured limit set by the operating system on virtual memory available to applications. Use of this option might be appropriate in large memory configurations in which the memory usage requirements of SQL Server are atypical and the virtual address space of the SQL Server process is totally in use. Incorrect use of this option can lead to conditions under which an instance of SQL Server may not start or may encounter run-time errors. Use the default for the -g parameter unless you see any of the following warnings in the SQL Server error log:
Using a value lower than the default will increase the amount of memory available to the buffer pool and thread stacks; this may, in turn, provide some performance benefit to memory-intensive workloads in systems that do not use many extended stored procedures, distributed queries, or automation objects. |
| -h | Reserves virtual address space for Hot Add memory metadata when AWE is enabled with 32-bit SQL Server. Required for Hot-Add memory with 32-bit AWE, but consumes about 500 MB of virtual address space and makes memory tuning more difficult. Not required for 64-bit SQL Server. Hot Add Memory is only available for Windows Server 2003, Enterprise and Datacenter editions. It also requires special hardware support from the hardware vendor. |
| -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 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 SQL Server in single-user mode enables any member of the computer's local Administrators group to connect to the instance of SQL Server as a member of the sysadmin fixed server role. For more information, see Troubleshooting: Connecting to SQL Server When System Administrators Are Locked Out. For more information about single-user mode, see Starting SQL Server in Single-User Mode. |
| -m"Client Application Name" | When you use the -m option with SQLCMD or SQL Server Management Studio, you can limit the connections to a specified client application. For example, -m"SQLCMD" limits connections to a single connection and that connection must identify itself as the SQLCMD
client program. Use this option when you are starting SQL Server in
single-user mode and an unknown client application is taking the only
available connection. To connect through the Query Editor in Management
Studio, use -m"Microsoft SQL Server Management Studio - Query". Client Application Name is case sensitive.
Important
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 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 are not logged. |
| -s | Allows you to start a named instance of SQL Server. 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 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). |
| -x | Disables the following monitoring features:
Caution
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 should not be used in other applications because it might adversely affect performance. This option is not supported in 32-bit releases of SQL Server. |
| Note |
|---|
| 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 SQL Server, but this sets other internal trace flags that are required only by SQL Server support engineers. (Parameters specified in the Control Panel startup window are not read.) |
Using 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 –m or –f options is most easily done at the command line, while manually starting sqlservr.exe.
| Note |
|---|
| When SQL Server is started by using net start, startup options use a slash (/) instead of a hyphen (-). |
Using Startup Options During Normal Operations
You may want to use some startup options every time you start SQL Server. These options, such as –g
or starting with a trace flag, are most easily done by configuring the
startup parameters by using SQL Server Management Studio or SQL Server
Configuration Manager. These tools save the startup options as registry
keys, enabling SQL Server to always start with the startup options
No comments:
Post a Comment