如何在SQL Server中排查长时间未提交的修改事务-SQL-PHP中文网

sql server中排查长事务阻塞需优先识别open_transaction_count>0且status='sleeping'的悬挂事务，因其不释放锁、阻碍日志截断；应结合last_request_end_time、program_name、dbcc opentran及wait_type综合判断真忙或假死，再决定是否kill。

如何在sql server中排查长时间未提交的修改事务

查 open_transaction_count > 0 的 sleeping 会话

很多长事务问题不是“正在执行”，而是“执行完了但没提交”，表现为 sys.dm_exec_requests 中 open_transaction_count > 0 且 status = 'sleeping'。这种会话不会主动释放锁，也不会推进日志截断，是日志爆满和阻塞的常见元凶。

执行以下查询快速定位：

SELECT r.session_id, r.status, r.command, r.wait_type, r.open_transaction_count,
       s.login_name, s.host_name, s.program_name,
       t.text AS last_sql,
       s.last_request_end_time
FROM sys.dm_exec_requests r
JOIN sys.dm_exec_sessions s ON r.session_id = s.session_id
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) t
WHERE r.open_transaction_count > 0 AND s.status = 'sleeping';

重点看 last_request_end_time：如果是几小时甚至几天前的时间，基本可判定为应用异常退出或代码漏写 COMMIT/ROLLBACK
program_name 和 host_name 能帮你快速反向定位到哪台应用服务器、哪个服务进程
不要只依赖 sp_who2 的 blk 列——它只反映瞬时阻塞，而这类 sleeping 事务可能根本不阻塞别人，却卡死日志复用

用 DBCC OPENTRAN 定位最老活跃事务

DBCC OPENTRAN 是 SQL Server 原生命令，专用于找出数据库中“最早未提交事务”的起始时间、SPID 和状态，比轮询 DMV 更直接可靠。

在目标数据库上下文中执行：

USE [YourDBName];
GO
DBCC OPENTRAN;

输出中关键字段：

OLDACT_SPID：持有该事务的会话 ID（若为 NULL，说明是系统内部事务，如复制）
OLDACT_STARTTIME：事务开始时间，与当前时间差就是持续秒数
OLDACT_NAME：事务名（如果有命名），否则显示 user_transaction

注意：DBCC OPENTRAN 只返回一个事务（最老的那个），但它往往是日志无法截断的真正瓶颈——因为只要它不提交，所有 VLF 都不能被标记为可重用。

确认是否真忙：看 wait_type 和磁盘 I/O

不能一看到 open_transaction_count > 0 就 KILL。得先判断它是“假死”还是“真忙”：

如果 status = 'running' 且 wait_type IN ('WRITELOG', 'ASYNC_IO_COMPLETION')：说明事务正在密集写日志或刷盘，大概率是大事务（如大批量 UPDATE/INSERT）。此时 KILL 可能引发长时间回滚，应优先检查磁盘延迟（sys.dm_io_virtual_file_stats）、日志文件是否在慢盘上
如果 status = 'runnable' 且 wait_type LIKE 'LCK_%'（如 LCK_M_U）：说明它正抢锁失败，要立刻查谁持锁：SELECT * FROM sys.dm_os_waiting_tasks WHERE session_id = @spid
如果 status = 'sleeping' 且 wait_type = 'SLEEP_TASK' 或为空：基本可认定为代码缺陷导致的“悬挂事务”，KILL 风险极低

为什么 SET XACT_ABORT ON 必须加在存储过程开头

大量长事务残留，根源是某条语句报错后后续逻辑跳过，ROLLBACK 根本没执行到。比如：

CREATE PROC p_test AS
BEGIN TRAN
UPDATE t1 SET x = 1 WHERE id = 999;
IF @@ERROR  0 RETURN; -- 错误后直接退出，TRAN 没回滚！
COMMIT;

加 SET XACT_ABORT ON 后，任何运行时错误（含客户端取消、超时、主键冲突）都会强制终止整个批处理并自动回滚事务：

必须放在 CREATE PROC 之后、BEGIN 之前，否则不生效
它不改变业务逻辑，只兜底异常路径；没有副作用，建议所有含事务的存储过程都加
配合 TRY...CATCH 更稳妥，但 XACT_ABORT 是最低成本的保命措施

真正棘手的不是怎么查，而是查到后不敢动——比如你发现一个开了 8 小时的 sleeping 事务，但 last_sql 显示是某个 ERP 系统的同步作业，这时候得先确认它是否在做跨库一致性校验，而不是直接 KILL。日志空间和锁资源的争夺，往往卡在业务语义的灰色地带。