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:
cross apply sys.dm_exec_sql_text(sql_handle)
where status = 'sleeping'
and open_tran = 1;
BEGIN TRAN statement increments the
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
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.