如何监控孤立的事务

[weixin_38060626]

前段应用程序执行某个操作之后，由于报错或者超时，导致会话状态变成了sleeping，但是开启的事务并没有提交或者回滚。目前能抓取到这个会话的SPID，和最后一次执行的语句。有没有什么好的办法能抓取到这个会话执行的完整的sql，以便迅速定位问题。因为只抓到最后一次执行的语句有时候定位问题太难了。
方法1 开启profiler  筛选事务ID>0的，缺点：抓取的数据太多，对性能产生影响
方法2，等问题出现的时候，通过抓取阻塞信息，获取SPID，然后根据SPID再去开profiler。缺点：需要人为干预
这个问题一直没有很好的办法来解决。

[weixin_38075024]

感谢回复，这个语句也只能抓出最后一次提交的语句，不能抓出完整的SQL
完整的 SQL  只能开 profile 了，再根据时间点和 SPID 去对应，通常没什么必要
DBCC INPUTBUFFER(spid) 结合我的那个查询的信息，通常足以定位程序了，毕竟你程序某个功能会用到什么 SQL，通常还是比较能够对应的，不至于弄一个可以提交任意 SQL 的应用程序

[weixin_38075729]

有时候只能抓到一些无关紧要的语句，或者这个语句在程序里面出现的频率非常高，，，这样排查问题就很不方便了

[weixin_38076612]

针对你这个情况，我建议你对频繁出现的相似sql做一个标记
update xx set f='a' where zz='x' --我是标记1 update xx set f='a' where zz='x' --我是标记2 update xx set f='a' where zz='x' --我是标记....
这样就很容易定位执行位置了。family as water

[weixin_38078612]

嗯，非常不错的思路。，但是如果这个语句有几十个以上就很麻烦了，另外有时候抓取到的语句是那种毫无特征性的

[weixin_38080355]

感谢回复，这个语句也只能抓出最后一次提交的语句，不能抓出完整的SQL
完整的就算 profile 也头疼的，比如就随便在连接中开一个事务，然后一句一句慢慢提交处理需求，等提交完几千个请求之后，我就什么也不做了，也不提交事务，这时候你拿到抓的数据结果也又能怎么样
所以完如果考虑极端情况，那确实是很难的，只能说尽量通过诸如加注释，区分帐号，app, host, client PID 这些手段去缩小范围，再通过小范围的 profile + 业务特征去定位吧

[weixin_38087646]

因为我们公司是做数据库监控产品的。在监控客户的系统的时候，经常出现这个问题。现实是：账号(他们一般都不区别经常用一个).APP ,host 这些有时间抓不到。其实就算这些抓到了也很恼火。因为代码不是我们的。客户当然希望一下就能够准确定位到问题.如果是自己维护的系统，当时很好说。人为的跟几次，肯定能定位清楚。

[weixin_38097166]

那确实没什么好办法，像我说的那种极端情况很抓瞎
如果仅从实现的角度，可以考虑程序做 profiler，这个 smo 有提供相关的类
你可以程序开一个 profiler, 接收 trace 到的数据，然后判断语句是否开事务了，如果开了，把相同 spid + host + client PID 的后续数据记录下来，如果后续数据有 commit，那么释放
如果后续没有读到 commit, 在达到预设的空闲时间后查 sysprocess 看是否孤立事务，如果是，那么这个数据存下来并 kill  进程
这样应该能够抓到完整的数据供分析，但 profile 显然对服务器是有压力的，所以其实并不建议长时

[weixin_38101849]

我比较倾向于设置成出错自动回滚想不想时已是想，不如不想都不想。

[weixin_38101916]

是想要出错自动回滚，但是某些程序代码忘记加入try catch 这样的异常捕捉，导致无法自动回滚。现在是想找到这样的代码

[weixin_38104149]

你不是有transaction吗？可以设置成事务中出错自动回滚的。想不想时已是想，不如不想都不想。

[weixin_38109394]

你不是有transaction吗？可以设置成事务中出错自动回滚的。

想不想时已是想，不如不想都不想。

你说的设置是在什么地方设置，存储过程还是代码里面

[weixin_38111004]

sp_configure 里面设
存储过程/代码里面也可以，但是就得每段代码都去加。直接系统全局设置比较方便。想不想时已是想，不如不想都不想。

[weixin_38124179]

EXEC sp_configure 'user options', 16384; 
GO 
RECONFIGURE ;
如果你原来设置过user options，把值或上16384设置进去。想不想时已是想，不如不想都不想。

[weixin_38062190]

EXEC sp_configure 'user options', 16384; 
GO 
RECONFIGURE ;
如果你原来设置过user options，把值或上16384设置进去。想不想时已是想，不如不想都不想。

[weixin_38135532]

非常不错的想法，之前没这么想过，，这个全局选项，对于程序中SQL的语句出现错误（不是存储过程的）也同样有效吗

[weixin_38112462]

具体是哪个值能说下吗，谢谢

[weixin_38139601]

如果程序没有去set option，就有效。当然，前提是有事务，否则也没法回滚前面的。
话说你没去看过 sp_configure 有些啥内容吗？想不想时已是想，不如不想都不想。

[weixin_38141713]

很多之前看过的，也忘记了，，熟悉的是常用的那几个。这个选项我之前真没用过。之前都是在会话级别使用。SET XACT_ABORT

[weixin_38144192]

有空可以翻翻文档，不用记住，大概知道要用的时候可以去哪儿找。想不想时已是想，不如不想都不想。

[weixin_38149527]

再请教一个问题，这个选项在生产上面开过了吗