SQL Server – Why Is sql_text ‘select @@trancount’ in Open but Sleeping Transaction?

sql serversql server 2014

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?

Best Answer

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.
Related Question