THE SQL Server Blog Spot on the Web

Welcome to - The SQL Server blog spot on the web Sign in | |
in Search

Adam Machanic

Adam Machanic, Boston-based SQL Server developer, shares his experiences with programming, monitoring, and performance tuning SQL Server. And the occasional battle with the query optimizer.

The Almighty Transaction (A Month of Activity Monitoring, Part 12 of 30)


Published Tuesday, April 12, 2011 11:00 AM by Adam Machanic



Adam Machanic said:

This post is part 13 of a 30-part series about the Who is Active stored procedure. A new post will run

April 13, 2011 10:44 AM

Adam Machanic said:

This post is part 30 of a 30-part series about the Who is Active stored procedure. A new post will run

April 30, 2011 11:45 AM

Chuck said:


Is there any reason that a session would have a large negative number for [open_tran_count]?  I have a session on one server that currently has an [open_tran_count] value of -10,891.  It would seem like this could only be a positive value since SQL Server doesn't allow a commit to be issued without a corresponding begin.  



September 8, 2014 3:55 PM

Adam Machanic said:

Hi Chuck,

The data comes from the open_tran column of sys.sysprocesses, which is a smallint. If a session comes along and runs BEGIN TRAN 32,769 times, it'll go negative. I just tried it :-)

So... sounds like a bug in an app on your end. Or a SQL Server bug. I'm not aware of any with that column, which certainly doesn't mean that none exist. But I think it's more likely to be an app bug.


September 8, 2014 4:54 PM

Chuck said:

Thanks Adam.  I appreciate the quick response.  That points me in the right direction.

September 9, 2014 8:23 AM
New Comments to this post are disabled

About Adam Machanic

Adam Machanic is a Boston-based SQL Server developer, writer, and speaker. He focuses on large-scale data warehouse performance and development, and is author of the award-winning SQL Server monitoring stored procedure, sp_WhoIsActive. Adam has written for numerous web sites and magazines, including SQLblog, Simple Talk, Search SQL Server, SQL Server Professional, CoDe, and VSJ. He has also contributed to several books on SQL Server, including "SQL Server 2008 Internals" (Microsoft Press, 2009) and "Expert SQL Server 2005 Development" (Apress, 2007). Adam regularly speaks at conferences and training events on a variety of SQL Server topics. He is a Microsoft Most Valuable Professional (MVP) for SQL Server, a Microsoft Certified IT Professional (MCITP), and an alumnus of the INETA North American Speakers Bureau.

This Blog


Privacy Statement