3

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?

4
  • For quite a while SSMS warns you if you close a window and it has open transactions. I've never looked at how it is implemented but I presume this may be for that feature. Aug 12, 2016 at 19:48
  • 1
    Yeah, the SSMS team confirmed that SSMS does this after each execution now instead of at close window time. Aug 13, 2016 at 12:55
  • any idea why the change? Is the fact that there is an open transaction now displayed in the UI? Aug 13, 2016 at 13:27
  • Looks like this was fixed in the most recent release Fixed an issue where SSMS runs a query to SELECT @@trancount after executing a batch. Sep 24, 2016 at 16:30

3 Answers 3

3

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

2
  • The other columns in sysprocesses point directly at the session id of the query window where I executed the BEGIN TRAN. Aug 12, 2016 at 18:34
  • 1
    Updated: Looks like this new behavior in SSMS 2016.
    – AMtwo
    Aug 12, 2016 at 18:46
-1

@@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.

6
  • But I didn't run that query it in the BEGIN TRAN window. Aug 12, 2016 at 17:59
  • The key part of the reproduction is to not run SELECT @@trancount anywhere. I'm still seeing it reported as the last thing that was run in the first session. My question is why? I guess you're seeing different behavior than I am. Aug 12, 2016 at 18:03
  • Check for any login triggers. Aug 12, 2016 at 18:09
  • No login triggers. Aug 12, 2016 at 18:30
  • Any database level triggers on your default database? (or whichever one you are connected to) Aug 12, 2016 at 18:34

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.