用于数据库管理员的诊断连接Diagnostic Connection for Database Administrators

适用于: 是SQL Server是Azure SQL 数据库否Azure SQL 数据仓库否并行数据仓库APPLIES TO: yesSQL Server yesAzure SQL Database noAzure SQL Data Warehouse noParallel Data Warehouse

SQL ServerSQL Server 为管理员提供了一种特殊的诊断连接,以供在无法与服务器建立标准连接时使用。provides a special diagnostic connection for administrators when standard connections to the server are not possible. 即使在 SQL ServerSQL Server 不响应标准连接请求时,管理员也可以使用此诊断连接访问 SQL ServerSQL Server ,以便执行诊断查询并解决问题。This diagnostic connection allows an administrator to access SQL ServerSQL Server to execute diagnostic queries and troubleshoot problems even when SQL ServerSQL Server is not responding to standard connection requests.

此专用管理员连接 (DAC) 支持 SQL ServerSQL Server的加密功能和其他安全功能。This dedicated administrator connection (DAC) supports encryption and other security features of SQL ServerSQL Server. DAC 只允许将用户上下文切换到其他管理用户。The DAC only allows changing the user context to another admin user.

SQL ServerSQL Server 尽力使 DAC 连接成功,但在非常特殊的情况下也可能会出现连接失败。makes every attempt to make DAC connect successfully, but under extreme situations it may not be successful.

适用范围:SQL ServerSQL ServerSQL Server 2008SQL Server 2008SQL Server 2017SQL Server 2017)、SQL 数据库SQL DatabaseApplies to: SQL ServerSQL Server ( SQL Server 2008SQL Server 2008 through SQL Server 2017SQL Server 2017), SQL 数据库SQL Database.

使用 DAC 连接Connecting with DAC

默认情况下,只能从服务器上运行的客户端建立连接。By default, the connection is only allowed from a client running on the server. 不允许进行网络连接,除非它们是使用带 remote admin connections 选项的 sp_configure 存储过程配置的。Network connections are not permitted unless they are configured by using the sp_configure stored procedure with the remote admin connections option.

只有 SQL ServerSQL Server sysadmin 角色的成员可以使用 DAC 进行连接。Only members of the SQL ServerSQL Server sysadmin role can connect using the DAC.

通过使用专用的管理员开关 ( -A ) 的sqlcmd命令提示实用工具,可以支持和使用 DAC。The DAC is available and supported through the sqlcmd command-prompt utility using a special administrator switch (-A). 有关使用 sqlcmd 的详细信息,请参阅将 sqlcmd 与脚本变量结合使用For more information about using sqlcmd, see Use sqlcmd with Scripting Variables. 还可以将前缀 admin: 连接到格式为 sqlcmd -S admin:<instance_name> 的实例名。You can also connect prefixing admin: to the instance name in the format sqlcmd -S admin:<instance_name>. 也可以通过连接到 admin:<instance_name> ,从 SQL Server Management StudioSQL Server Management Studio 查询编辑器启动 DAC。You can also initiate a DAC from a SQL Server Management StudioSQL 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 ServerSQL Server实例只允许使用一个 DAC。To guarantee that there are resources available for the connection, only one DAC is allowed per instance of SQL ServerSQL Server. 如果 DAC 连接已经激活,则通过 DAC 进行连接的任何新请求都将被拒绝,并出现错误 17810。If a DAC connection is already active, any new request to connect through the DAC is denied with error 17810.

  • 为了保留资源, SQL Server ExpressSQL Server Express 不侦听 DAC 端口,除非使用跟踪标志 7806 进行启动。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

    由于只要启动 数据库引擎Database Engine 的实例,就能保证 master 数据库处于可用状态,因此建议使用 DAC 连接到 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 Engine is started.

  • SQL ServerSQL 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 ServerSQL Server 所处的状态,DAC 会话可能会在闩锁上被阻塞。However, depending on the state that the SQL ServerSQL 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 ServerSQL ServerIn that case, your only option may be to restart SQL ServerSQL Server.

  • 为保证连接成功并排除 DAC 故障, SQL ServerSQL Server 保留了一定的资源用于处理 DAC 上运行的命令。To guarantee connectivity and troubleshooting with the DAC, SQL ServerSQL 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 ServerSQL Server的状态,KILL 命令并非一定会成功;如果失败,则唯一的选择是重新启动 SQL ServerSQL ServerDepending on the state of SQL ServerSQL Server, the KILL command might not always succeed; then the only option may be to restart SQL ServerSQL 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 ServerSQL Server 在 TCP 端口 1434(如果可用)上侦听 DAC,或者在 数据库引擎Database Engine 启动时动态分配的 TCP 端口上侦听 DAC。listens for the DAC on TCP port 1434 if available or a TCP port dynamically assigned upon 数据库引擎Database 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 ServerSQL Server ,并且客户端可以立即远程连接到 DAC。After the remote administration connection is configured, the DAC listener is enabled without requiring a restart of SQL ServerSQL Server and a client can now connect to the DAC remotely. 通过先在本地使用 DAC 连接到 SQL ServerSQL Server ,然后再执行 sp_configure 存储过程接受远程连接,则即使 SQL ServerSQL Server 停止响应,DAC 侦听器仍然可以接受远程连接。You can enable the DAC listener to accept connections remotely even if SQL ServerSQL Server is unresponsive by first connecting to SQL ServerSQL 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 ServerSQL Server 停止响应并且未启用 DAC 侦听器,则可能必须重新启动 SQL ServerSQL Server 来连接 DAC。If SQL ServerSQL Server is unresponsive and the DAC listener is not enabled, you might have to restart SQL ServerSQL Server to connect with the DAC. 因此,建议在群集系统上启用 remote admin connections 配置选项。Therefore, we recommend that you enable the remote admin connections configuration option on clustered systems.

DAC 端口由 SQL ServerSQL Server 在启动时动态分配。The DAC port is assigned dynamically by SQL ServerSQL Server during startup. 当连接到默认实例时,DAC 会避免在连接时对 SQL Server Browser 服务使用 SQL ServerSQL Server 解决协议 (SSRP) 请求。When connecting to the default instance, the DAC avoids using a SQL ServerSQL 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 ServerSQL Server 浏览器没有侦听 SSRP 请求,则连接请求将返回错误。If SQL ServerSQL 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 ServerSQL Server 配置为接受远程管理连接,则必须使用显式端口号启动 DAC:If SQL ServerSQL 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 ServerSQL Server 错误日志列出了 DAC 的端口号,默认情况下为 1434。The SQL ServerSQL Server error log lists the port number for the DAC, which is 1434 by default. 如果将 SQL ServerSQL Server 配置为只接受本地 DAC 连接,请使用以下命令和环回适配器进行连接:If SQL ServerSQL 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 数据库SQL Database 的类似示例将使用包括 -d 参数的以下命令指定数据库:A similar example connecting to SQL 数据库SQL 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

将 sqlcmd 与脚本变量结合使用Use 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)