Why is sql_text “select @@trancount” in open but sleeping transaction?

Question

Using SQL Server Management Studio, open a query window and execute

BEGIN TRAN

In another window execute

select [text],
from sys.sysprocesses
cross apply sys.dm_exec_sql_text(sql_handle)
where status = 'sleeping'
and open_tran = 1

I see select @@trancount

What’s up with this? Who’s counting transactions? Is this a Management Studio thing?

asked 2016-08-12 by Michael J Swart

---

Answer

I don’t think it’s a Management Studio thing–I’ve seen this with other application before, but haven’t personally seen it with SSMS. That being said, I wouldn’t be surprised if SSMS did something like this in some scenario.

If you grab a few more columns from sys.sysprocesses, you might be able to track back to who is counting transactions–in particular, if it is the SSMS query window (ie, something you did), or SSMS doing it’s own thing:

select [text]
from sys.sysprocesses
cross apply sys.dm_exec_sql_text(sql_handle)
where status = 'sleeping'
and open_tran = 1;

The BEGIN TRAN statement increments the @@trancount and open_tran counters–but until you do “stuff” it doesn’t actually hold any locks, cause any blocking, or prevent transaction log reuse. Because of this, I’ve stopped using the open_tran column as a reason to worry. Instead, I look at the specific transaction state in sys.dm_database_transactions:

SELECT t.[text], p.spid, p.hostname, p.loginame, p.program_name, dt.*
FROM sys.sysprocesses p
cross apply sys.dm_exec_sql_text(sql_handle) t
JOIN sys.dm_tran_session_transactions st ON p.spid = st.session_id
JOIN sys.dm_tran_database_transac   tions dt ON st.transaction_id = dt.transaction_id

You can use the database_transaction_state and other columns to get more useful information to decide if you need to worry about it.

[EDIT]: It looks like this is something new to SSMS 2016: If I use SSMS 2014, I am not seeing the behavior you describe, but when I use SSMS 2016, I see exactly that behavior. It looks like SSMS 2016 does a select @@trancount implicitly after you run BEGIN TRAN or any query in a query window. Profiler/Extended Events confirms that there is a Batch Started/Completed for the BEGIN TRAN or whatever, immediately followed by a second Batch Started/Completed for the select @@trancount. Weird.

answered 2016-08-12 by Andy Mallon