Database Engine 服務啟動選項Database Engine Service Startup Options

本主題適用於:是SQL Server否Azure SQL Database否Azure SQL 資料倉儲 否平行處理資料倉儲 THIS TOPIC APPLIES TO: yesSQL ServernoAzure SQL DatabasenoAzure SQL Data Warehouse noParallel Data Warehouse

啟動選項指定啟動期間所需的特定檔案位置,並指定一些整個伺服器範圍的條件。Startup options designate certain file locations needed during startup, and specify some server wide conditions. 除非您疑難排解 Database EngineDatabase Engine ,或是發生異常問題而「 [SQL Server]SQL Server 客戶支援」指示您使用啟動選項,否則大部分使用者都不需要指定啟動選項。Most users do not need to specify startup options unless you are troubleshooting the Database EngineDatabase Engine or you have an unusual problem and are directed to use a startup option by [SQL Server]SQL Server Customer Support.

警告

不正確使用啟動選項,可能會影響伺服器效能,而且可能會導致 [SQL Server]SQL Server 無法啟動。Improper use of startup options can affect server performance and can prevent [SQL Server]SQL Server from starting.

以 "mssql" 使用者身分啟動 Linux 上的 SQL Server,以免未來發生啟動問題。Start SQL Server on Linux with the "mssql" user to prevent future startup issues. 範例:sudo -u mssql /opt/mssql/bin/sqlservr [STARTUP OPTIONS]Example: sudo -u mssql /opt/mssql/bin/sqlservr [STARTUP OPTIONS]

關於啟動選項About Startup Options

安裝 [SQL Server]SQL Server時,安裝程式會在 MicrosoftMicrosoft Windows 登錄中寫入一組預設啟動選項。When you install [SQL Server]SQL Server, Setup writes a set of default startup options in the MicrosoftMicrosoft Windows registry. 您可使用這些啟動選項來指定替代 master 資料庫檔案、master 資料庫記錄檔或錯誤記錄檔。You can use these startup options to specify an alternate master database file, master database log file, or error log file. 如果 Database EngineDatabase Engine 找不到必要檔案,就不會啟動 [SQL Server]SQL ServerIf the Database EngineDatabase Engine cannot locate the necessary files, [SQL Server]SQL Server will not start.

您可以使用 [SQL Server]SQL Server 組態管理員來設定啟動選項。Startup options can be set by using [SQL Server]SQL Server Configuration Manager. 如需相關資訊,請參閱設定伺服器啟動選項 (SQL Server 組態管理員)For information, see Configure Server Startup Options (SQL Server Configuration Manager).

啟動選項清單List of Startup Options

預設啟動選項Default startup options

選項。Options 描述Description
-d master_file_path-d master_file_path master 資料庫檔案的完整路徑 (通常是 C:\Program Files\Microsoft SQL Server\MSSQL.n\MSSQL\Data\master.mdf)。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-e error_log_path 這是錯誤記錄檔的完整路徑 (通常是 C:\Program Files\Microsoft SQL Server\MSSQL.n\MSSQL\LOG\ERRORLOG)。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-l master_log_path master 資料庫記錄檔的完整路徑 (通常是 C:\Program Files\Microsoft SQL Server\MSSQL.n\MSSQL\Data\mastlog.ldf)。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.

其他啟動選項Other startup options

選項。Options 描述Description
-c-c 縮短從命令提示字元啟動 [SQL Server]SQL Server 的啟動時間。Shortens startup time when starting [SQL Server]SQL Server from the command prompt. 一般而言, SQL Server Database EngineSQL Server Database Engine 會呼叫「服務控制管理員」,以服務方式啟動。Typically, the SQL Server Database EngineSQL Server Database Engine starts as a service by calling the Service Control Manager. 因為 SQL Server Database EngineSQL Server Database Engine 從命令提示字元啟動時不會以服務方式啟動,所以請使用 -c 略過這個步驟。Because the SQL Server Database EngineSQL Server Database Engine does not start as a service when starting from the command prompt, use -c to skip this step.
-f-f 啟動只含最小組態的 [SQL Server]SQL Server 執行個體。Starts an instance of [SQL Server]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. 以最低組態模式啟動 [SQL Server]SQL Server 會將 [SQL Server]SQL Server 放在單一使用者模式下。Starting [SQL Server]SQL Server in minimal configuration mode places [SQL Server]SQL Server in single-user mode. 如需詳細資訊,請參閱後續的 -m 描述。For more information, see the description for -m that follows.
-g memory_to_reserve-g memory_to_reserve 指定 [SQL Server]SQL Server 會保留可在 [SQL Server]SQL Server 處理序中進行記憶體配置,但在 max_server_memory 伺服器設定的 [SQL Server]SQL Server 記憶體集區以外的可用記憶體,以整數 MB 為單位。Specifies an integer number of megabytes (MB) of memory that [SQL Server]SQL Server will leave available for memory allocations within the [SQL Server]SQL Server process, but outside the [SQL Server]SQL Server memory pool set by max_server_memory server setting. 記憶體集區外的記憶體是 [SQL Server]SQL Server 用來載入項目的區域,例如擴充程序 .dll 檔、分散式查詢參考的 OLE DB 提供者,以及 Transact-SQLTransact-SQL 陳述式中參考的自動化物件。The memory outside of the memory pool is the area used by [SQL Server]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-SQLTransact-SQL statements. 預設值是 256 MB。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. [SQL Server]SQL Server 的記憶體使用需求不合規則且 [SQL Server]SQL Server 處理序的虛擬位址空間全部都在使用的大型記憶體組態中,可能適合使用這個選項。Use of this option might be appropriate in large memory configurations in which the memory usage requirements of [SQL Server]SQL Server are atypical and the virtual address space of the [SQL Server]SQL Server process is totally in use. 使用此選項不正確時,可能會造成無法啟動 [SQL Server]SQL Server 執行個體的狀況,也可能會發生執行階段錯誤。Incorrect use of this option can lead to conditions under which an instance of [SQL Server]SQL Server may not start or may encounter run-time errors.

除非您在 錯誤記錄檔中見到下列任何警告,否則,請使用 -g [SQL Server]SQL Server 參數的預設值:Use the default for the -g parameter unless you see any of the following warnings in the [SQL Server]SQL Server error log:
"Failed Virtual Allocate Bytes: FAIL_VIRTUAL_RESERVE <大小>""Failed Virtual Allocate Bytes: FAIL_VIRTUAL_RESERVE <size>"
"Failed Virtual Allocate Bytes: FAIL_VIRTUAL_COMMIT <大小>""Failed Virtual Allocate Bytes: FAIL_VIRTUAL_COMMIT <size>"

這些訊息可能表示 [SQL Server]SQL Server 正在嘗試釋出 [SQL Server]SQL Server 記憶體集區的可用部分,以便找出擴充預存程序 .dll 檔或 Automation 物件等項目的空間。These messages might indicate that [SQL Server]SQL Server is trying to free parts of the [SQL Server]SQL Server memory pool in order to find space for items, such as extended stored procedure .dll files or automation objects. 在這種情況下,可考慮加大 -g 參數所保留的記憶體總數量。In this case, consider increasing the amount of memory reserved by the -g switch.

使用的值若小於預設值,會增加 SQL Server Memory Manager 所管理之記憶體集區與執行緒堆疊可用的記憶體大小,使得系統中不使用許多擴充預存程序、分散式查詢或 Automation 物件的記憶體密集工作負載可以因此而改善一些效能。Using a value lower than the default will increase the amount of memory available to the memory pool managed by the SQL Server Memory Manager 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.
-m-m 在單一使用者模式中啟動 [SQL Server]SQL Server 執行個體。Starts an instance of [SQL Server]SQL Server in single-user mode. 以單一使用者模式啟動 [SQL Server]SQL Server 執行個體時,只有單一使用者可以進行連接,且不會啟動 CHECKPOINT 處理序。When you start an instance of [SQL Server]SQL Server in single-user mode, only a single user can connect, and the CHECKPOINT process is not started. CHECKPOINT 會保證將交易規律地從磁碟快取區寫到資料庫裝置。CHECKPOINT guarantees that completed transactions are regularly written from the disk cache to the database device. (一般而言,如果遇到一些應該修復系統資料庫的問題時,就會使用這個選項)。這個選項會啟用 sp_configure allow updates 選項。(Typically, this option is used if you experience problems with system databases that should be repaired.) Enables the sp_configure allow updates option. 根據預設,allow updates 是停用的。By default, allow updates is disabled. 在單一使用者模式下啟動 [SQL Server]SQL Server 可讓電腦本機管理員群組的任何成員以 sysadmin 固定伺服器角色的成員身分,連接到 [SQL Server]SQL Server 的執行個體。Starting [SQL Server]SQL Server in single-user mode enables any member of the computer's local Administrators group to connect to the instance of [SQL Server]SQL Server as a member of the sysadmin fixed server role. 如需詳細資訊,請參閱 當系統管理員遭到鎖定時連接到 SQL Server。如需單一使用者模式的詳細資訊,請參閱 以單一使用者模式啟動 SQL ServerFor 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.
-m用戶端應用程式名稱-mClient Application Name 限制與所指定用戶端應用程式的連接。Limits the connections to a specified client application. 例如, -mSQLCMD 會將連接限制為單一連接,而且該連接必須將自己識別為 SQLCMD 用戶端程式。For example, -mSQLCMD limits connections to a single connection and that connection must identify itself as the SQLCMD client program. 當您在單一使用者模式下啟動 [SQL Server]SQL Server 而且有未知的用戶端應用程式佔用唯一可用的連接時,請使用這個選項。Use this option when you are starting [SQL Server]SQL Server in single-user mode and an unknown client application is taking the only available connection. 使用 "Microsoft SQL Server Management Studio - Query" 與 SSMS 查詢編輯器連接。Use "Microsoft SQL Server Management Studio - Query" to connect with the SSMS Query Editor. SSMS 查詢編輯器選項無法透過 [SQL Server]SQL Server Configuration Manager 進行設定,因為它包含此工具拒絕的虛線字元。The SSMS Query Editor option cannot be configured by using [SQL Server]SQL Server 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\sqlserver -s MSSQLSERVER -m"Microsoft SQL Server Management Studio - Query"

C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Binn\sqlserver -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-n 請不要使用 Windows 應用程式記錄檔來記錄 [SQL Server]SQL Server 事件。Does not use the Windows application log to record [SQL Server]SQL Server events. 若您使用 [SQL Server]SQL Server -n 啟動執行個體,建議您同時使用 -e 啟動選項。If you start an instance of [SQL Server]SQL Server with -n, we recommend that you also use the -e startup option. 否則,系統不會記錄 [SQL Server]SQL Server 事件。Otherwise, [SQL Server]SQL Server events are not logged.
-s-s 可讓您啟動 [SQL Server]SQL Server的具名執行個體。Allows you to start a named instance of [SQL Server]SQL Server. 若未設定 -s 參數,則會嘗試啟動預設執行個體。Without the -s parameter set, the default instance will try to start. 您必須先在命令提示字元處切換至該執行個體的適當 BINN 目錄,才能啟動 sqlservr.exeYou must switch to the appropriate BINN directory for the instance at a command prompt before starting sqlservr.exe. 例如,如果 Instance1 的二進位檔原本要使用 \mssql$Instance1,使用者就必須位於 \mssql$Instance1\binn 目錄才能啟動 sqlservr.exe -s instance1For 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#-T trace# 指出啟動 [SQL Server]SQL Server 執行個體時,應該已啟用指定的追蹤旗標 (trace#)。Indicates that an instance of [SQL Server]SQL Server should be started with a specified trace flag (trace#) in effect. 追蹤旗標用來啟動具有非標準行為的伺服器。Trace flags are used to start the server with nonstandard behavior. 如需詳細資訊,請參閱追蹤旗標 (Transact-SQL)For more information, see Trace Flags (Transact-SQL).

重要事項: 指定具有 -T 選項的追蹤旗標時,請使用大寫的 "T" 來傳送追蹤旗標編號。Important: When specifying a trace flag with the -T option, use an uppercase "T" to pass the trace flag number. [SQL Server]SQL Server會接受小寫 "t",但這會設定 [SQL Server]SQL Server 支援工程師才需要的其他內部追蹤旗標A lowercase "t" is accepted by [SQL Server]SQL Server, but this sets other internal trace flags that are required only by [SQL Server]SQL Server support engineers. (不會讀取控制台啟動視窗中所指定的參數)。(Parameters specified in the Control Panel startup window are not read.)
-x-x 停用下列監視功能:Disables the following monitoring features:
- [SQL Server]SQL Server 效能監視器計數器- [SQL Server]SQL Server performance monitor counters
- 保留 CPU 時間及快取命中比率統計資料- Keeping CPU time and cache-hit ratio statistics
- 收集 DBCC SQLPERF 命令的資訊- Collecting information for the DBCC SQLPERF command
- 收集某些動態管理檢視的資訊- Collecting information for some dynamic management views
- 許多擴充事件的事件點- Many extended-events event points

警告: 使用 -x 啟動選項時,可讓您用來診斷 [SQL Server]SQL Server 效能與運作問題的資訊會大幅減少。Warning: When you use the –x startup option, the information that is available for you to diagnose performance and functional problems with [SQL Server]SQL Server is greatly reduced.
-E-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. 32 位元的 [SQL Server]SQL Server版本中不支援這個選項。This option is not supported in 32-bit releases of [SQL Server]SQL Server.

使用啟動選項進行疑難排解Using Startup Options for Troubleshooting

有些啟動選項 (例如單一使用者模式和最低組態模式) 主要是在疑難排解期間使用。Some startup options, such as single-user mode and minimal configuration mode, are principally used during troubleshooting. 手動啟動 sqlservr.exe 時,使用 –m–f 選項來啟動伺服器進行疑難排解是在命令列中最容易的作業。Starting the server for troubleshooting with the –m or –f options is easiest at the command line, while manually starting sqlservr.exe.

注意

使用 [SQL Server]SQL Server net start 啟動時,啟動選項會使用斜線 (/),而非連字號 (-)。When [SQL Server]SQL Server is started by using net start, startup options use a slash (/) instead of a hyphen (-).

在正常作業期間使用啟動選項Using Startup Options During Normal Operations

在您每次啟動 [SQL Server]SQL Server時,可能都會想要使用一些啟動選項。You may want to use some startup options every time you start [SQL Server]SQL Server. 只要使用 組態管理員來設定啟動參數,很容易就可以完成這些選項 (例如 –g [SQL Server]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]SQL Server Configuration Manager. 這些工具會將啟動選項儲存成登錄機碼,這樣 [SQL Server]SQL Server 就一定會使用這些啟動選項來啟動。These tool saves the startup options as registry keys, enabling [SQL Server]SQL Server to always start with the startup options.

相容性支援Compatibility Support

不支援 -h SQL Server 2017SQL Server 2017參數。The -h parameter is not supported in SQL Server 2017SQL Server 2017. 舊版 32 位元 [SQL Server]SQL Server 執行個體使用此參數,在啟用 AWE 的狀況下保留 Hot Add Memory 中繼資料的虛擬記憶體位址空間。This parameter was used in earlier versions of 32-bit instances of [SQL Server]SQL Server to reserve virtual memory address space for Hot Add memory metadata when AWE is enabled. 如需詳細資訊,請參閱 SQL Server 2016 中已取代及已中止的 SQL Server 功能For more information, see Discontinued SQL Server Features in SQL Server 2016.

設定 scan for startup procs 伺服器組態選項Configure the scan for startup procs Server Configuration Option
啟動、停止、暫停、繼續、重新啟動資料庫引擎、SQL Server Agent 或 SQL Server Browser 服務 設定伺服器啟動選項 (SQL Server 組態管理員)Start, Stop, Pause, Resume, Restart the Database Engine, SQL Server Agent, or SQL Server Browser Service Configure Server Startup Options (SQL Server Configuration Manager)

另請參閱See Also

CHECKPOINT (Transact-SQL) CHECKPOINT (Transact-SQL)
sqlservr 應用程式sqlservr Application