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.
Fixed an issue where SSMS runs a query to SELECT @@trancount after executing a batch.