http://sqlblog.com/blogs/adam_machanic/archive/2011/04/07/who-is-active-default-columns-day-1-a-month-of-activity-monitoring-part-7-of-30.aspxThis post is part 7 of a 30-part series about the Who is Active stored procedure. A new post will run each day during the month of April, 2011. After April all of these posts will be edited and combined into a single document to become the basis of theenCommunityServer 2.1 SP2 (Build: 61129.1)http://sqlblog.com/blogs/adam_machanic/archive/2011/04/07/who-is-active-default-columns-day-1-a-month-of-activity-monitoring-part-7-of-30.aspx#34813Mon, 11 Apr 2011 23:27:11 GMT21093a07-8b3d-42db-8cbf-3350fcbf5496:34813Michael Zilberstein<p>You say that only sysprocesses shows number of open transactions. What about open_transaction_count column in sys.dm_exec_requests? I guess, we'll see the difference only for idle session which keeps open transaction(s) - dm_exec_requests would show nothing while sysprocesses would contain row.</p> http://sqlblog.com/blogs/adam_machanic/archive/2011/04/07/who-is-active-default-columns-day-1-a-month-of-activity-monitoring-part-7-of-30.aspx#34816Tue, 12 Apr 2011 01:35:40 GMT21093a07-8b3d-42db-8cbf-3350fcbf5496:34816Adam Machanic<p>Michael, exactly. sysprocesses will show the value for a sleeping session. And that's really where it's interesting. Imagine that an app starts a bunch of nested transactions, then doesn't commit them all, but is done sending requests. This column is invaluable for debugging in that situation.</p> http://sqlblog.com/blogs/adam_machanic/archive/2011/04/07/who-is-active-default-columns-day-1-a-month-of-activity-monitoring-part-7-of-30.aspx#34830Tue, 12 Apr 2011 13:57:50 GMT21093a07-8b3d-42db-8cbf-3350fcbf5496:34830Michael Zilberstein<p>Adam,</p> <p>I actually don't have to imagine - two of my clients had exactly this problem when connection from application isn't closed properly after failing on timeout, thus leaving open transaction and locked resources. I don't use your procedure (strictly speaking, I can't according to the license terms - or at least that's questionable) but it was so bad (some of the main tables locked for quite long) that I've build my own monitoring for it 2 years ago that automatically killed stuck sessions and notified application developers. Here is first time I found somebody mentioning this issue:</p> <p><a rel="nofollow" target="_new" href="http://blogs.msdn.com/b/psssql/archive/2008/07/23/how-it-works-attention-attention-or-should-i-say-cancel-the-query-and-be-sure-to-process-your-results.aspx">http://blogs.msdn.com/b/psssql/archive/2008/07/23/how-it-works-attention-attention-or-should-i-say-cancel-the-query-and-be-sure-to-process-your-results.aspx</a></p> <p>(comment from mz1313 is mine)</p> http://sqlblog.com/blogs/adam_machanic/archive/2011/04/07/who-is-active-default-columns-day-1-a-month-of-activity-monitoring-part-7-of-30.aspx#35335Sat, 30 Apr 2011 15:45:04 GMT21093a07-8b3d-42db-8cbf-3350fcbf5496:35335Adam Machanic<p>This post is part 30 of a 30-part series about the Who is Active stored procedure. A new post will run</p>