KILL (Transact-SQL)KILL (Transact-SQL)

适用对象: 是SQL Server 是Azure SQL 数据库 是Azure Synapse Analytics (SQL DW) 是并行数据仓库 APPLIES TO: yesSQL Server yesAzure SQL Database yesAzure Synapse Analytics (SQL DW) yesParallel Data Warehouse

结束基于会话 ID 或工作单元 (UOW) 的用户进程。Ends a user process that is based on the session ID or unit of work (UOW). 如果指定的会话 ID 或 UOW 有许多工作要撤消,KILL 语句可能需要一段时间才能完成。If the specified session ID or UOW has much work to undo, the KILL statement may take some time to complete. 此过程可能需要更长时间才能完成,特别是在进程涉及回滚长事务时。The process takes longer to complete particularly when the process involves rolling back a long transaction.

KILL 结束正常连接,这会在内部停止与指定会话 ID 关联的事务。KILL ends a normal connection, which internally stops the transactions that are associated with the specified session ID. 有时,可能会使用 MicrosoftMicrosoft 分布式事务处理协调器 (MS DTC)。At times, MicrosoftMicrosoft Distributed Transaction Coordinator (MS DTC) might be in use. 如果使用 MS DTC,也可以使用此语句来结束孤立的未决分布式事务。If MS DTC is in use, you can also use the statement to end orphaned and in-doubt distributed transactions.

“主题链接”图标 Transact-SQL 语法约定Topic link icon Transact-SQL Syntax Conventions

语法Syntax

-- Syntax for SQL Server  
  
KILL { session ID | UOW } [ WITH STATUSONLY ]   
-- Syntax for Azure SQL Data Warehouse and Parallel Data Warehouse  
  
KILL 'session_id'  
[;]   

参数Arguments

session ID session ID
要结束的进程的会话 ID。Is the session ID of the process to end. session ID 是在建立连接时为每个用户连接分配的唯一整数 (int) 。session ID is a unique integer (int) that is assigned to each user connection when the connection is made. 在连接期间,会话 ID 值与该连接捆绑在一起。The session ID value is tied to the connection for the duration of the connection. 连接结束时,则释放该整数值,并且可以将它重新分配给新的连接。When the connection ends, the integer value is released and can be reassigned to a new connection.
以下查询可帮助确定想要终止的 session_idThe following query can help you identify the session_id that you want to kill:

SELECT conn.session_id, host_name, program_name,
    nt_domain, login_name, connect_time, last_request_end_time 
FROM sys.dm_exec_sessions AS sess
JOIN sys.dm_exec_connections AS conn
   ON sess.session_id = conn.session_id;

UOW UOW
适用于:(SQL Server 2008SQL Server 2008 及更高版本Applies to: (SQL Server 2008SQL Server 2008 and later

标识分布式事务的工作单元 ID (UOW)。Identifies the Unit of Work ID (UOW) of distributed transactions. UOW 是可以从 sys.dm_tran_locks 动态管理视图的 request_owner_guid 列获取的 GUID 。UOW is a GUID that may be obtained from the request_owner_guid column of the sys.dm_tran_locks dynamic management view. 也可以从错误日志中或通过 MS DTC 监视器获取 UOW 。UOW also can be obtained from the error log or through the MS DTC monitor. 有关监视分布式事务的详细信息,请参阅 MS DTC 文档。For more information about monitoring distributed transactions, see the MS DTC documentation.

使用 KILL UOW 可停止孤立的分布式事务。Use KILL UOW to stop orphaned distributed transactions. 这些事务不与任何实际会话 ID 相关联,但与会话 ID =“-2”虚拟关联。These transactions aren't associated with any real session ID, but instead are associated artificially with session ID = '-2'. 可使标识孤立事务变得更为简单,其方法是查询 sys.dm_tran_locks、sys.dm_exec_sessions 或 sys.dm_exec_requests 动态管理视图中的会话 ID 列。This session ID makes it easier to identify orphaned transactions by querying the session ID column in sys.dm_tran_locks, sys.dm_exec_sessions, or sys.dm_exec_requests dynamic management views.

WITH STATUSONLYWITH STATUSONLY
生成由于更早的 KILL 语句而正在回滚的指定会话 ID 或 UOW 的进度报告。Generates a progress report about a specified session ID or UOW that is being rolled back because of an earlier KILL statement. KILL WITH STATUSONLY 不结束或回滚会话 ID 或 UOW 。KILL WITH STATUSONLY doesn't end or roll back the session ID or UOW. 此命令只显示当前回滚进度。The command only displays the current progress of the rollback.

RemarksRemarks

KILL 常用于结束使用锁来阻止其他重要进程的进程。KILL is commonly used to end a process that is blocking other important processes with locks. KILL 还可用于停止执行使用必要系统资源的查询的进程。KILL can also be used to stop a process that is executing a query that is using necessary system resources. 无法结束系统进程和运行扩展存储过程的进程。System processes and processes running an extended stored procedure can't be ended.

应当小心使用 KILL,特别是正在运行重要进程时。Use KILL carefully, especially when critical processes are running. 你无法终止自己的进程。You can't kill your own process. 也不得终止以下进程:You also shouldn't kill the following processes:

  • AWAITING COMMANDAWAITING COMMAND
  • CHECKPOINT SLEEPCHECKPOINT SLEEP
  • LAZY WRITERLAZY WRITER
  • LOCK MONITORLOCK MONITOR
  • SIGNAL HANDLERSIGNAL HANDLER

使用 @@SPID 可显示当前会话的 session ID 值。Use @@SPID to display the session ID value for the current session.

若要获取有效会话 ID 值的报告,请查询 sys.dm_tran_locks、sys.dm_exec_sessions 和 sys.dm_exec_requests 动态管理视图中的 session_id 列。To obtain a report of active session ID values, query the session_id column of the sys.dm_tran_locks, sys.dm_exec_sessions, and sys.dm_exec_requests dynamic management views. 也可以查看 sp_who 系统存储过程返回的 SPID 列。You can also view the SPID column that the sp_who system stored procedure returns. 如果特定 SPID 的回滚正在进行,则该 SPID 的 sp_who 结果集中的 cmd 列指示 KILLED/ROLLBACK。If a rollback is in progress for a specific SPID, the cmd column in the sp_who result set for that SPID indicates KILLED/ROLLBACK.

当特定的连接在数据库资源上有锁并阻塞其他连接的进程时,blocking_session_idsys.dm_exec_requests 列或 blk 返回的 sp_who 列中将显示该阻塞连接的会话 ID。When a particular connection has a lock on a database resource and blocks the progress of another connection, the session ID of the blocking connection shows up in the blocking_session_id column of sys.dm_exec_requests or the blk column returned by sp_who.

KILL 命令可用于解决有疑问的分布式事务。The KILL command can be used to resolve in-doubt distributed transactions. 这些事务是未解决的分布式事务,它们是由于无计划地重新启动数据库服务器或 MS DTC 协调器而产生的。These transactions are unresolved distributed transactions that occur because of unplanned restarts of the database server or MS DTC coordinator. 有关未决事务的详细信息,请参阅使用标记的事务一致地恢复相关的数据库的事务(完全恢复模式)中的“两阶段提交”一节。For more information about in-doubt transactions, see the "Two-Phase Commit" section in Use Marked Transactions to Recover Related Databases Consistently (Full Recovery Model).

使用 WITH STATUSONLYUsing WITH STATUSONLY

如果会话 ID 或 UOW 由于前面的 KILL session ID |UOW 语句而回滚,KILL WITH STATUSONLY 生成报告。KILL WITH STATUSONLY generates a report if the session ID or UOW rolls back because of a previous KILL session ID|UOW statement. 进度报告指出已完成的回滚量(以百分比形式)和估计的剩余时间(以秒为单位)。The progress report states the amount of rollback completed (in percent) and the estimated length of time left (in seconds). 报告使用以下格式声明它:The report states it in the following form:

Spid|UOW <xxx>: Transaction rollback in progress. Estimated rollback completion: <yy>% Estimated time left: <zz> seconds

如果在 KILL session ID |UOW WITH STATUSONLY 语句运行前会话 ID 或 UOW 的回滚就已完成,那么 KILL session ID |UOW WITH STATUSONLY 返回以下错误:If the rollback of the session ID or UOW finishes before the KILL session ID|UOW WITH STATUSONLY statement runs, KILL session ID|UOW WITH STATUSONLY returns the following error:

"Msg 6120, Level 16, State 1, Line 1"  
"Status report cannot be obtained. Rollback operation for Process ID <session ID> is not in progress."

如果没有要回滚的会话 ID 或 UOW,也会生成此错误This error also occurs if no session ID or UOW is being rolled back

通过重复不使用 WITH STATUSONLY 选项的同一 KILL session ID |UOW 语句,可以获得相同的状态报告。The same status report can be obtained by repeating the same KILL session ID|UOW statement without using the WITH STATUSONLY option. 不过,不建议这样重复使用选项。However, we don't recommend repeating the option this way. 如果在新的 KILL 语句运行前回滚就已完成且会话 ID 已重新分配给新任务,那么重复 KILL session ID 语句可能会停止新进程。If you repeat a KILL session ID statement, the new process might stop if the rollback finishes and the session ID is reassigned to a new task before the new KILL statement runs. 通过指定 WITH STATUSONLY 来阻止新进程停止。Prevent the new process from stopping by specifying WITH STATUSONLY.

权限Permissions

SQL ServerSQL Server: 要求具有 ALTER ANY CONNECTION 权限。SQL ServerSQL Server: Requires the ALTER ANY CONNECTION permission. ALTER ANY CONNECTION 包括在 sysadmin 或 processadmin 固定服务器角色的成员身份中。ALTER ANY CONNECTION is included with membership in the sysadmin or processadmin fixed server roles.

SQL 数据库SQL Database: 需要具有 KILL DATABASE CONNECTION 权限。SQL 数据库SQL Database: Requires the KILL DATABASE CONNECTION permission. 服务器级别主体登录名具有 KILL DATABASE CONNECTION。The server-level principal login has the KILL DATABASE CONNECTION.

示例Examples

A.A. 使用 KILL 停止会话Using KILL to stop a session

下面的示例展示了如何停止会话 ID 53The following example shows how to stop session ID 53.

KILL 53;  
GO  

B.B. 使用 KILL session ID WITH STATUSONLY 获取进度报告Using KILL session ID WITH STATUSONLY to obtain a progress report

以下示例为特定的会话 ID 生成回滚进程的状态。The following example generates a status of the rollback process for the specific session ID.

KILL 54;  
KILL 54 WITH STATUSONLY;  
GO  
  
--This is the progress report.  
spid 54: Transaction rollback in progress. Estimated rollback completion: 80% Estimated time left: 10 seconds.  

C.C. 使用 KILL 停止孤立的分布式事务Using KILL to stop an orphaned distributed transaction

下面的示例展示了如何停止孤立的分布式事务(会话 ID = -2),其中 UOW 为 D5499C66-E398-45CA-BF7E-DC9C194B48CFThe following example shows how to stop an orphaned distributed transaction (session ID = -2) with a UOW of D5499C66-E398-45CA-BF7E-DC9C194B48CF.

KILL 'D5499C66-E398-45CA-BF7E-DC9C194B48CF';  

另请参阅See Also

KILL STATS JOB (Transact-SQL) KILL STATS JOB (Transact-SQL)
KILL QUERY NOTIFICATION SUBSCRIPTION (Transact-SQL) KILL QUERY NOTIFICATION SUBSCRIPTION (Transact-SQL)
内置函数 (Transact-SQL) Built-in Functions (Transact-SQL)
SHUTDOWN (Transact-SQL) SHUTDOWN (Transact-SQL)
@@SPID (Transact-SQL) @@SPID (Transact-SQL)
sys.dm_exec_requests (Transact-SQL) sys.dm_exec_requests (Transact-SQL)
sys.dm_exec_sessions (Transact-SQL) sys.dm_exec_sessions (Transact-SQL)
sys.dm_tran_locks (Transact-SQL) sys.dm_tran_locks (Transact-SQL)
sp_lock (Transact-SQL) sp_lock (Transact-SQL)
sp_who (Transact-SQL)sp_who (Transact-SQL)