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?

Answer 1

This is a SQL Server Management Studio thing. At least SSMS version 13.0.15500.91. This version of SSMS will count the number of open transactions on your behalf using the same connection as your query window.

Here's how I found out,

• I connected to another server running 2012 and also saw SELECT @@TRANCOUNT
• Using a 2012 version of SSMS, I connected to a variety of servers and saw BEGIN TRAN
• I used profiler and saw that SSMS 2016 executes SELECT @@TRANCOUNT after every execution.
• Incidentally, SSMS 2016 seems to execute SELECT SERVERPROPERTY('EngineEdition') AS DatabaseEngineEdition before every execution.
• BEGIN TRAN turns out to be a bit of a red herring. SSMS 2016 executes these extra queries no matter what gets executed in the query window... even if your batch is just whitespace.
• I coded an c# application that uses ado.net and profiled it. It does not execute these extra queries.

Answer 2

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.

Answer 3

@@trancount is per session so you would see a value of 1 if you ran it in the BEGIN TRAN window.

You are seeing a sleeping entry for the first session because it's not actually doing anything. The transaction is open but no work is being done. It would be the same thing if you opened a transaction, did a bunch of work (that finished) but never actually closed the transaction.

Ok, I think I understand your question a bit better now. I saw a BEGIN TRAN the first time I ran your query, then I tried running SELECT @@trancount in the first session. When I came back to the second session I then saw the SELECT @@trancount like you did.

It's going to show you the very last thing that was run in that first session.