檢視或變更伺服器屬性 (SQL Server)View or Change Server Properties (SQL Server)

適用於: 是SQL Server 否Azure SQL Database 否Azure Synapse Analytics (SQL DW) 否平行處理資料倉儲 APPLIES TO: yesSQL Server noAzure SQL Database noAzure Synapse Analytics (SQL DW) noParallel Data Warehouse

本主題描述如何使用 SQL ServerSQL ServerSQL Server Management StudioSQL Server Management Studio或 SQL Server 組態管理員檢視或變更 Transact-SQLTransact-SQL執行個體的屬性。This topic describes how to view or change the properties of an instance of SQL ServerSQL Server by using SQL Server Management StudioSQL Server Management Studio, Transact-SQLTransact-SQL, or SQL Server Configuration Manager.

本主題內容In This Topic

開始之前Before You Begin

限制事項Limitations and Restrictions

  • 使用 sp_configure 時,您必須在設定組態選項之後,執行 RECONFIGURE 或 RECONFIGURE WITH OVERRIDE。When using sp_configure, you must run either RECONFIGURE or RECONFIGURE WITH OVERRIDE after setting a configuration option. RECONFIGURE WITH OVERRIDE 陳述式通常是保留給應該非常小心使用的組態選項。The RECONFIGURE WITH OVERRIDE statement is usually reserved for configuration options that should be used with extreme caution. 但是 RECONFIGURE WITH OVERRIDE 對所有組態選項都有效,所以它可以取代 RECONFIGURE。However, RECONFIGURE WITH OVERRIDE works for all configuration options, and you can use it in place of RECONFIGURE.

    注意

    RECONFIGURE 會在交易中執行。RECONFIGURE executes within a transaction. 如果任何重新設定作業失敗,所有重新設定作業都不會生效。If any of the reconfigure operations fail, none of the reconfigure operations will take effect.

  • 有些屬性頁面會透過 Windows Management Instrumentation (WMI) 取得資訊。Some property pages present information obtained via Windows Management Instrumentation (WMI). 若要顯示這些頁面,您必須將 WMI 安裝在執行 SQL Server Management StudioSQL Server Management Studio的電腦上。To display those pages, WMI must be installed on the computer running SQL Server Management StudioSQL Server Management Studio.

安全性Security

權限Permissions

如需詳細資訊,請參閱 伺服器層級角色For more information, see Server-Level Roles.

依預設,所有使用者都會取得不含參數或只含第一個參數之 sp_configure 的執行權限。Execute permissions on sp_configure with no parameters or with only the first parameter are granted to all users by default. 以同時設定兩個參數的 sp_configure 來變更組態選項或執行 RECONFIGURE 陳述式時,使用者必須取得 ALTER SETTINGS 伺服器層級權限。To execute sp_configure with both parameters to change a configuration option or to run the RECONFIGURE statement, a user must be granted the ALTER SETTINGS server-level permission. 系統管理員 (sysadmin)serveradmin 固定伺服器角色會隱含 ALTER SETTINGS 權限。The ALTER SETTINGS permission is implicitly held by the sysadmin and serveradmin fixed server roles.

使用 SQL Server Management StudioUsing SQL Server Management Studio

檢視或變更伺服器屬性To view or change server properties

  1. 在物件總管中,以滑鼠右鍵按一下伺服器,然後按一下 [屬性] 。In Object Explorer, right-click a server, and then click Properties.

  2. [伺服器屬性] 對話方塊中,按一下頁面以檢視或變更有關該頁面的伺服器資訊。In the Server Properties dialog box, click a page to view or change server information about that page. 部分屬性是唯讀的。Some properties are read-only.

使用 Transact-SQLUsing Transact-SQL

若要使用 SERVERPROPERTY 內建函數檢視伺服器屬性To view server properties by using the SERVERPROPERTY built-in function

  1. 連接到 Database EngineDatabase EngineConnect to the Database EngineDatabase Engine.

  2. 在標準列中,按一下 [新增查詢]From the Standard bar, click New Query.

  3. 複製下列範例並將其貼到查詢視窗中,然後按一下 [執行]Copy and paste the following example into the query window and click Execute. 這個範例會在 陳述式中使用 SERVERPROPERTY SELECT 內建函數傳回目前伺服器的相關資訊。This example uses the SERVERPROPERTY built-in function in a SELECT statement to return information about the current server. 當 Windows 伺服器安裝了多個 SQL ServerSQL Server 執行個體,且用戶端必須開啟另一項連接來連到目前連接所用的相同執行個體時,這個狀況非常有用。This scenario is useful when there are multiple instances of SQL ServerSQL Server installed on a Windows-based server, and the client must open another connection to the same instance that is used by the current connection.

    SELECT CONVERT( sysname, SERVERPROPERTY('servername'));  
    GO  
    

若要使用 sys.servers 類別目錄檢視表檢視伺服器屬性To view server properties by using the sys.servers catalog view

  1. 連接到 Database EngineDatabase EngineConnect to the Database EngineDatabase Engine.

  2. 在標準列中,按一下 [新增查詢]From the Standard bar, click New Query.

  3. 複製下列範例並將其貼到查詢視窗中,然後按一下 [執行]Copy and paste the following example into the query window and click Execute. 這個範例會查詢 sys.servers 目錄檢視,以傳回目前伺服器的名稱 (name) 和識別碼 (server_id),以及用來連接到連結之伺服器的 OLE DB 提供者名稱 (provider)。This example queries the sys.servers catalog view to return the name (name) and ID (server_id) of the current server, and the name of the OLE DB provider (provider) for connecting to a linked server.

    USE AdventureWorks2012;   
    GO  
    SELECT name, server_id, provider  
    FROM sys.servers ;   
    GO  
    
    

若要使用 sys.configurations 類別目錄檢視表檢視伺服器屬性To view server properties by using the sys.configurations catalog view

  1. 連接到 Database EngineDatabase EngineConnect to the Database EngineDatabase Engine.

  2. 在標準列中,按一下 [新增查詢]From the Standard bar, click New Query.

  3. 複製下列範例並將其貼到查詢視窗中,然後按一下 [執行]Copy and paste the following example into the query window and click Execute. 這個範例會查詢 sys.configurations 類別目錄檢視,以傳回目前伺服器上每個伺服器組態選項的相關資訊。This example queries the sys.configurations catalog view to return information about each server configuration option on the current server. 此範例會傳回選項的名稱 (name) 和描述 (description),以及選項是否為進階選項 (is_advanced)。The example returns the name (name) and description (description) of the option and whether the option is an advanced option (is_advanced).

    USE AdventureWorks2012;   
    GO  
    SELECT name, description, is_advanced  
    FROM sys.configurations ;   
    GO  
    
    

使用 sp_configure 變更伺服器屬性To change a server property by using sp_configure

  1. 連接到 Database EngineDatabase EngineConnect to the Database EngineDatabase Engine.

  2. 在標準列中,按一下 [新增查詢]From the Standard bar, click New Query.

  3. 複製下列範例並將其貼到查詢視窗中,然後按一下 [執行]Copy and paste the following example into the query window and click Execute. 這個範例示範如何使用 sp_configure 變更伺服器屬性。This example shows how to use sp_configure to change a server property. 此範例會將 fill factor 選項的值變更為 100The example changes the value of the fill factor option to 100. 伺服器必須重新啟動,變更才會生效。The server must be restarted before the change can take effect.

Use AdventureWorks2012;  
GO  
sp_configure 'show advanced options', 1;  
GO  
RECONFIGURE;  
GO  
sp_configure 'fill factor', 100;  
GO  
RECONFIGURE;  
GO  

如需詳細資訊,請參閱 伺服器組態選項 (SQL Server)的電腦上。For more information, see Server Configuration Options (SQL Server).

使用 SQL Server 組態管理員Using SQL Server Configuration Manager

部分伺服器屬性可以使用 SQL Server 組態管理員檢視或變更。Some server properties can be viewed or changed by using SQL Server Configuration Manager. 例如,您可以檢視 SQL Server 執行個體的版本和版別,或是變更錯誤記錄檔儲存的位置。For example, you can view the version and edition of the instance of SQL Server, or change the location where error log files are stored. 您也可以藉由查詢 伺服器相關的動態管理檢視與函數的方式檢視這些屬性。These properties can also be viewed by querying the Server-Related Dynamic Management Views and Functions.

檢視或變更伺服器屬性To view or change server properties

  1. 指向 [開始] 功能表上的 [所有程式] ,然後依序指向 [ Microsoft SQL ServerMicrosoft SQL Server] 和 [組態工具] ,再按一下 [SQL Server 組態管理員]On the Start menu, point to All Programs, point to Microsoft SQL ServerMicrosoft SQL Server, point to Configuration Tools, and then click SQL Server Configuration Manager.

  2. [SQL Server 組態管理員] 中,按一下 [SQL Server 服務]In SQL Server Configuration Manager, click SQL Server Services.

  3. 在詳細資料窗格中,以滑鼠右鍵按一下 [SQL Server (<執行個體名稱>)] ,然後按一下 [屬性] 。In the details pane, right-click SQL Server (<instancename>), and then click Properties.

  4. 在 [SQL Server (<執行個體名稱>) 屬性] 對話方塊中,變更 [服務] 索引標籤或 [進階] 索引標籤上的伺服器屬性,然後按一下 [確定] 。In the SQL Server (<instancename>) Properties dialog box, change the server properties on the Service tab or the Advanced tab, and then click OK.

後續操作:變更伺服器屬性之後Follow Up: After you change server properties

對於某些屬性,伺服器可能必須重新啟動,變更才會生效。For some properties, the server might have to be restarted before the change can take effect.

另請參閱See Also

伺服器組態選項 (SQL Server) Server Configuration Options (SQL Server)
SET 陳述式 (Transact-SQL) SET Statements (Transact-SQL)
SERVERPROPERTY (Transact-SQL) SERVERPROPERTY (Transact-SQL)
sp_configure (Transact-SQL) sp_configure (Transact-SQL)
RECONFIGURE (Transact-SQL) RECONFIGURE (Transact-SQL)
SELECT (Transact-SQL) SELECT (Transact-SQL)
設定 WMI 在 SQL Server 工具中顯示伺服器狀態 Configure WMI to Show Server Status in SQL Server Tools
SQL Server 組態管理員 SQL Server Configuration Manager
組態函式 (Transact-SQL) Configuration Functions (Transact-SQL)
伺服器相關的動態管理檢視和函式 (Transact-SQL)Server-Related Dynamic Management Views and Functions (Transact-SQL)