关闭/终止交易 [英] close/kill transaction

问题描述

根据DBCC OPENTRAN，我有这个未结交易:

I have this open transaction, according to DBCC OPENTRAN:

Oldest active transaction:
SPID (server process ID) : 54
UID (user ID)            : -1
Name                     : UPDATE
LSN                      : (4196:12146:1)
Start time               : Jul 20 2011 12:44:23:590PM
SID                      : 0x01

有没有办法杀死它/回滚它?

Is there a way to kill it/ roll it back?

推荐答案

你应该首先弄清楚它在做什么，它来自哪里，如果适用，它可能会运行多长时间:

You should first figure out what it was doing, where it came from, and if applicable how much longer it might be expected to run:

SELECT 
   r.[session_id],
   c.[client_net_address],
   s.[host_name],
   c.[connect_time],
   [request_start_time] = s.[last_request_start_time],
   [current_time] = CURRENT_TIMESTAMP,
   r.[percent_complete],
   [estimated_finish_time] = DATEADD
       (
           MILLISECOND,
           r.[estimated_completion_time], 
           CURRENT_TIMESTAMP
       ),
   current_command = SUBSTRING
       (
           t.[text],
           r.[statement_start_offset]/2,
           COALESCE(NULLIF(r.[statement_end_offset], -1)/2, 2147483647)
       ),
   module = COALESCE(QUOTENAME(OBJECT_SCHEMA_NAME(t.[objectid], t.[dbid])) 
       + '.' + QUOTENAME(OBJECT_NAME(t.[objectid], t.[dbid])), '<ad hoc>'),
   [status] = UPPER(s.[status])
 FROM
     sys.dm_exec_connections AS c
 INNER JOIN
     sys.dm_exec_sessions AS s
     ON c.session_id = s.session_id
 LEFT OUTER JOIN
     sys.dm_exec_requests AS r
     ON r.[session_id] = s.[session_id]
 OUTER APPLY
     sys.dm_exec_sql_text(r.[sql_handle]) AS t
 WHERE
     c.session_id = 54;

如果您确信可以切断此连接，则可以使用:

If you are confident that you can sever this connection you can use:

KILL 54;

请注意，根据会话正在执行的操作，它可能会使数据和/或调用它的应用程序处于一种奇怪的状态.

Just be aware that depending on what the session was doing it could leave data and/or the app that called it in a weird state.

这篇关于关闭/终止交易的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！