資料庫管理員的診斷連接Diagnostic Connection for Database Administrators

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

[SQL Server]SQL Server 為系統管理員提供了特殊的診斷連接,可在伺服器的標準連接失效時使用。 provides a special diagnostic connection for administrators when standard connections to the server are not possible. 這個診斷連接可讓系統管理員存取 [SQL Server]SQL Server 以執行診斷查詢和排解疑難問題,即使 [SQL Server]SQL Server 未回應標準連接要求。This diagnostic connection allows an administrator to access [SQL Server]SQL Server to execute diagnostic queries and troubleshoot problems even when [SQL Server]SQL Server is not responding to standard connection requests.

此專用管理員連接 (DAC) 支援加密以及 [SQL Server]SQL Server的其他安全性功能。This dedicated administrator connection (DAC) supports encryption and other security features of [SQL Server]SQL Server. DAC 只允許將使用者內容變更為其他管理使用者。The DAC only allows changing the user context to another admin user.

[SQL Server]SQL Server 將不斷嘗試以便讓 DAC 順利連接,但是在極端的情況下可能無法成功。 makes every attempt to make DAC connect successfully, but under extreme situations it may not be successful.

適用於[SQL Server]SQL Server ( SQL Server 2008SQL Server 2008SQL Server 2017SQL Server 2017)、 SQL DatabaseSQL DatabaseApplies to: [SQL Server]SQL Server ( SQL Server 2008SQL Server 2008 through SQL Server 2017SQL Server 2017), SQL DatabaseSQL Database.

連接 DACConnecting with DAC

依預設,只能從執行於伺服器上的用戶端進行連接。By default, the connection is only allowed from a client running on the server. 除非使用 sp_configure 預存程序搭配 remote admin connections 選項來設定網路連接,否則不允許進行網路連接。Network connections are not permitted unless they are configured by using the sp_configure stored procedure with the remote admin connections option.

只有 [SQL Server]SQL Server 系統管理員 (sysadmin) 角色的成員可以使用 DAC 進行連接。Only members of the [SQL Server]SQL Server sysadmin role can connect using the DAC.

DAC 的存取與支援是使用特殊的系統管理員參數 ( -A ),透過sqlcmd命令提示字元公用程式來執行。The DAC is available and supported through the sqlcmd command-prompt utility using a special administrator switch (-A). 如需使用 sqlcmd 的詳細資訊,請參閱以指令碼變數使用 sqlcmdFor more information about using sqlcmd, see Use sqlcmd with Scripting Variables. 您也可以在執行個體名稱前面加上 admin: 來連線,其格式為 sqlcmd -S admin:<執行個體名稱>You can also connect prefixing admin: to the instance name in the format sqlcmd -S admin:<instance_name>. 您也可以連線到 admin:<執行個體名稱>,以便從 Transact-SQLSQL Server Management Studio 查詢編輯器起始 DAC。You can also initiate a DAC from a Transact-SQLSQL Server Management Studio Query Editor by connecting to admin:<instance_name>.

限制Restrictions

由於 DAC 僅在少數的情況下才會為了診斷伺服器問題而建立,因此這項連接有某些限制:Because the DAC exists solely for diagnosing server problems in rare circumstances, there are some restrictions on the connection:

  • 若要保證有足夠的資源可供連接使用,每個 [SQL Server]SQL Server執行個體只能有一個 DAC。To guarantee that there are resources available for the connection, only one DAC is allowed per instance of [SQL Server]SQL Server. 若已有作用中的 DAC 連接存在,則所有透過 DAC 建立連接的新要求都會遭到拒絕,並產生錯誤 17810。If a DAC connection is already active, any new request to connect through the DAC is denied with error 17810.

  • 為了節省資源,除非以追蹤旗標 7806 啟動,否則 SQL Server ExpressSQL Server Express 不會接聽 DAC 通訊埠。To conserve resources, SQL Server ExpressSQL Server Express does not listen on the DAC port unless started with a trace flag 7806.

  • DAC 最初會嘗試連接到與登入相關聯的預設資料庫。The DAC initially attempts to connect to the default database associated with the login. 連接成功後,您就可以連接到 master 資料庫。After it is successfully connected, you can connect to the master database. 如果預設資料庫離線或是無法使用,連接將會傳回錯誤 4060。If the default database is offline or otherwise not available, the connection will return error 4060. 但是,若您使用下列命令來覆寫預設資料庫以連接到 master 資料庫,連接就會成功:However, it will succeed if you override the default database to connect to the master database instead using the following command:

    sqlcmd –A –d mastersqlcmd –A –d master

    建議您使用 DAC 連接到 master 資料庫,因為只要啟動 Database EngineDatabase Engine 執行個體,就一定可以使用 master。We recommend that you connect to the master database with the DAC because master is guaranteed to be available if the instance of the Database EngineDatabase Engine is started.

  • [SQL Server]SQL Server 禁止以 DAC 執行平行查詢或命令。 prohibits running parallel queries or commands with the DAC. 例如,若您以 DAC 執行下列其中一項陳述式,就會產生錯誤 3637:For example, error 3637 is generated if you execute either of the following statements with the DAC:

    • RESTORERESTORE

    • BACKUPBACKUP

  • 只有某些限定的資源必定可透過 DAC 來使用。Only limited resources are guaranteed to be available with the DAC. 請勿使用 DAC 來執行需耗用大量資源的查詢 (例如,Do not use the DAC to run resource-intensive queries (for example. 大型資料表上的複雜聯結) 或可能會封鎖的查詢。a complex join on large table) or queries that may block. 如此可防止 DAC 在現有的伺服器問題外衍生出其他問題。This helps prevent the DAC from compounding any existing server problems. 為了避免潛在的封鎖情況,若您必須執行可能會封鎖的查詢,請盡可能在快照隔離等級下執行查詢;否則,請將交易隔離等級設為 READ UNCOMMITTED,並將 LOCK_TIMEOUT 值設為 2000 毫秒之類的短數值。To avoid potential blocking scenarios, if you have to run queries that may block, run the query under snapshot-based isolation levels if possible; otherwise, set the transaction isolation level to READ UNCOMMITTED and set the LOCK_TIMEOUT value to a short value such as 2000 milliseconds, or both. 如此可防止 DAC 工作階段產生封鎖的情形。This will prevent the DAC session from getting blocked. 但依照 [SQL Server]SQL Server 所處的狀態,DAC 工作階段也可能會遭到閂鎖封鎖。However, depending on the state that the [SQL Server]SQL Server is in, the DAC session might get blocked on a latch. 您可以使用 CTRL-C 來結束 DAC 工作階段,但不一定能成功。You might be able to terminate the DAC session using CTRL-C but it is not guaranteed. 在這種情況下,重新啟動 [SQL Server]SQL Server可能是唯一的選擇。In that case, your only option may be to restart [SQL Server]SQL Server.

  • 為了保證 DAC 連接與疑難排解能順利進行, [SQL Server]SQL Server 會保留有限的資源來處理 DAC 所執行的命令。To guarantee connectivity and troubleshooting with the DAC, [SQL Server]SQL Server reserves limited resources to process commands run on the DAC. 通常,這些資源僅足夠用來執行簡單的診斷與疑難排解功能,如下表所示。These resources are typically only enough for simple diagnostic and troubleshooting functions, such as those listed below.

    雖然,理論上您可以在 DAC 上執行任何不需要以平行方式執行的 Transact-SQLTransact-SQL 陳述式,但我們還是強烈建議您將使用方式限定在下列診斷與疑難排解命令:Although you can theoretically run any Transact-SQLTransact-SQL statement that does not have to execute in parallel on the DAC, we strongly recommend that you restrict usage to the following diagnostic and troubleshooting commands:

  • 基本診斷的查詢動態管理檢視,例如 sys.dm_tran_locks 可了解封鎖狀態、sys.dm_os_memory_cache_counters 可檢查快取的健全狀態,而 sys.dm_exec_requestssys.dm_exec_sessions 可了解作用中的工作階段與要求。Querying dynamic management views for basic diagnostics such as sys.dm_tran_locks for the locking status, sys.dm_os_memory_cache_counters to check the health of caches, and sys.dm_exec_requests and sys.dm_exec_sessions for active sessions and requests. 請避免使用會耗用大量資源 (例如 sys.dm_tran_version_store 會掃描完整版本存放區而產生大量 I/O) 或使用複雜聯結的動態管理檢視。Avoid dynamic management views that are resource intensive (for example, sys.dm_tran_version_store scans the full version store and can cause extensive I/O) or that use complex joins. 如需效能含意的資訊,請參閱特定 動態管理檢視的文件集。For information about performance implications, see the documentation for the specific dynamic management view.

  • 查詢目錄檢視。Querying catalog views.

  • 基本 DBCC 命令,例如 DBCC FREEPROCCACHEDBCC FREESYSTEMCACHEDBCC DROPCLEANBUFFERSDBCC SQLPERFBasic DBCC commands such as DBCC FREEPROCCACHE, DBCC FREESYSTEMCACHE, DBCC DROPCLEANBUFFERS, and DBCC SQLPERF. 請勿執行需要大量資源的命令,例如 DBCC CHECKDBDBCC DBREINDEXDBCC SHRINKDATABASEDo not run resource-intensive commands such as DBCC CHECKDB, DBCC DBREINDEX, or DBCC SHRINKDATABASE.

  • Transact-SQLTransact-SQL KILL<spid> 命令。 KILL<spid> command. 根據 [SQL Server]SQL Server的狀態而定,KILL 命令可能不會每次都成功,這時只能選擇重新啟動 [SQL Server]SQL ServerDepending on the state of [SQL Server]SQL Server, the KILL command might not always succeed; then the only option may be to restart [SQL Server]SQL Server. 下面是部分一般方針:The following are some general guidelines:

    • 藉由查詢 SELECT * FROM sys.dm_exec_sessions WHERE session_id = <spid>,來確認是否已確實清除 SPID。Verify that the SPID was actually killed by querying SELECT * FROM sys.dm_exec_sessions WHERE session_id = <spid>. 若未傳回資料列,表示已清除工作階段。If it returns no rows, it means the session was killed.

    • 若工作階段仍然存在,請執行 SELECT * FROM sys.dm_os_tasks WHERE session_id = <spid>查詢,以確認此工作階段上是否有指定的工作。If the session is still there, verify whether there are tasks assigned to this session by running the query SELECT * FROM sys.dm_os_tasks WHERE session_id = <spid>. 若您在工作階段上看到工作,很可能表示工作階段正在清除中。If you see the task there, most likely your session is currently being killed. 請注意,此作業可能會耗費大量時間,而且可能完全無法成功。Note that this may take considerable amount of time and may not succeed at all.

    • 若在與此工作階段相關聯的 sys.dm_os_tasks 中沒有工作存在,但工作階段在執行 KILL 命令後仍處於 sys.dm_exec_sessions 中,則表示您沒有可用的工作者。If there are no tasks in the sys.dm_os_tasks associated with this session, but the session remains in sys.dm_exec_sessions after executing the KILL command, it means that you do not have a worker available. 請選取目前正在執行的其中一個工作 (列示於 sys.dm_os_tasks 檢視表中而含有 sessions_id <> NULL的工作),然後清除與此工作相關聯的工作階段以釋放工作者。Select one of the currently running tasks (a task listed in the sys.dm_os_tasks view with a sessions_id <> NULL), and kill the session associated with it to free up the worker. 請注意,終止單一工作階段可能不夠:您可能必須清除多個工作階段。Note that it may not be enough to kill a single session: you may have to kill multiple ones.

DAC 通訊埠DAC Port

[SQL Server]SQL Server 如果在啟動 Database EngineDatabase Engine 之後有可用或動態指派的 TCP 通訊埠,會在 TCP 通訊埠 1434 上接聽 DAC。 listens for the DAC on TCP port 1434 if available or a TCP port dynamically assigned upon Database EngineDatabase Engine startup. 錯誤記錄檔包含 DAC 接聽時所使用的通訊埠編號。The error log contains the port number the DAC is listening on. 依預設,DAC 接聽程式只接受本機通訊埠上的連接。By default the DAC listener accepts connection on only the local port. 如需可啟動遠端管理連接的程式碼範例,請參閱 remote admin connections 伺服器組態選項For a code sample that activates remote administration connections, see remote admin connections Server Configuration Option.

一旦設定遠端管理連接之後,即會啟用 DAC 接聽程式而不需重新啟動 [SQL Server]SQL Server ,而且用戶端可以從遠端連接到 DAC。After the remote administration connection is configured, the DAC listener is enabled without requiring a restart of [SQL Server]SQL Server and a client can now connect to the DAC remotely. 您可以先在本機使用 DAC 連接到 [SQL Server]SQL Server ,然後執行 sp_configure 預存程序以接受遠端的連接,藉以啟用 DAC 接聽程式使其可接受遠端連接,即使 [SQL Server]SQL Server 未回應仍可執行。You can enable the DAC listener to accept connections remotely even if [SQL Server]SQL Server is unresponsive by first connecting to [SQL Server]SQL Server using the DAC locally, and then executing the sp_configure stored procedure to accept connection from remote connections.

在叢集組態中,DAC 預設為關閉。On cluster configurations, the DAC will be off by default. 使用者可執行 sp_configure 的 remote admin connection 選項,啟用 DAC 接聽程式以存取遠端連接。Users can execute the remote admin connection option of sp_configure to enable the DAC listener to access a remote connection. [SQL Server]SQL Server 未回應且 DAC 接聽程式未啟用,您可能必須重新啟動 [SQL Server]SQL Server 以連接 DAC。If [SQL Server]SQL Server is unresponsive and the DAC listener is not enabled, you might have to restart [SQL Server]SQL Server to connect with the DAC. 因此,建議您在叢集系統上啟用 remote admin connections 組態選項。Therefore, we recommend that you enable the remote admin connections configuration option on clustered systems.

[SQL Server]SQL Server 會在啟動期間動態指定 DAC 通訊埠。The DAC port is assigned dynamically by [SQL Server]SQL Server during startup. 連接到預設執行個體時,DAC 會在連接時避免對 SQL Server Browser 服務使用 [SQL Server]SQL Server 解析通訊協定 (SSRP) 要求。When connecting to the default instance, the DAC avoids using a [SQL Server]SQL Server Resolution Protocol (SSRP) request to the SQL Server Browser Service when connecting. 它會先透過 TCP 通訊埠 1434 連接。It first connects over TCP port 1434. 若失敗,則會發出 SSRP 呼叫以取得通訊埠。If that fails, it makes an SSRP call to get the port. [SQL Server]SQL Server Browser 並未接聽 SSRP 要求,則連接要求會傳回錯誤。If [SQL Server]SQL Server Browser is not listening for SSRP requests, the connection request returns an error. 請參閱錯誤記錄檔,以了解 DAC 接聽時所使用的通訊埠編號。Refer to the error log to find the port number DAC is listening on. [SQL Server]SQL Server 的組態可接受遠端管理連接,DAC 就必須以明確的通訊埠編號起始:If [SQL Server]SQL Server is configured to accept remote administration connections, the DAC must be initiated with an explicit port number:

sqlcmd –S tcp:<server>,<port>sqlcmd –S tcp:<server>,<port>

[SQL Server]SQL Server 錯誤記錄檔會列出 DAC 的通訊埠編號,依預設為 1434。The [SQL Server]SQL Server error log lists the port number for the DAC, which is 1434 by default. 若將 [SQL Server]SQL Server 設定為只接受本機 DAC 連接,請利用下列命令使用回送配接器進行連接:If [SQL Server]SQL Server is configured to accept local DAC connections only, connect using the loopback adapter using the following command:

sqlcmd –S 127.0.0.1,1434sqlcmd –S 127.0.0.1,1434

提示

當搭配 DAC 連接到 Azure SQL DatabaseAzure SQL Database 時,您也必須使用 -d 選項在連接字串中指定資料庫名稱。When connecting to the Azure SQL DatabaseAzure SQL Database with the DAC, you must also specify the database name in the connection string by using the -d option.

範例Example

在此範例中,系統管理員注意到伺服器 URAN123 並未回應,而要診斷問題。In this example, an administrator notices that server URAN123 is not responding and wants to diagnose the problem. 為了進行這項作業,使用者啟動 sqlcmd 命令提示字元公用程式,並使用 URAN123 來表示 DAC,連接到伺服器 -ATo do this, the user activates the sqlcmd command prompt utility and connects to server URAN123 using -A to indicate the DAC.

sqlcmd -S URAN123 -U sa -P <xxx> –A

此時,系統管理員可執行查詢以診斷問題,並盡可能結束未回應的工作階段。The administrator can now execute queries to diagnose the problem and possibly terminate the unresponsive sessions.

連接到 SQL DatabaseSQL Database 的類似範例是使用下列包含 -d 參數的命令來指定資料庫:A similar example connecting to SQL DatabaseSQL Database would use the following command including the -d parameter to specify the database:

sqlcmd -S serverName.database.windows.net,1434 -U sa -P <xxx> -d AdventureWorks

以指令碼變數使用 sqlcmdUse sqlcmd with Scripting Variables
sqlcmd 公用程式sqlcmd Utility
SELECT (Transact-SQL)SELECT (Transact-SQL)
sp_who (Transact-SQL)sp_who (Transact-SQL)
sp_lock (Transact-SQL)sp_lock (Transact-SQL)
KILL (Transact-SQL)KILL (Transact-SQL)
DBCC CHECKALLOC (Transact-SQL)DBCC CHECKALLOC (Transact-SQL)
DBCC CHECKDB (Transact-SQL)DBCC CHECKDB (Transact-SQL)
DBCC OPENTRAN (Transact-SQL)DBCC OPENTRAN (Transact-SQL)
DBCC INPUTBUFFER (Transact-SQL)DBCC INPUTBUFFER (Transact-SQL)
伺服器組態選項 (SQL Server)Server Configuration Options (SQL Server)
交易相關的動態管理檢視和函數 (Transact-SQL)Transaction Related Dynamic Management Views and Functions (Transact-SQL)
追蹤旗標 (Transact-SQL)Trace Flags (Transact-SQL)