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?
Asked2016-08-12byMichael 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.
Answered2016-08-12byAndy Mallon