Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > comp.databases.ms-sqlserver > #1469

Re: How to find not-committed transaction in SQL Server 2008 exactly?

Show all headers | View raw

Peng Liu (liupengwyy@gmail.com) writes:
> Now I did a minor change to my C++ code. Now, on the SQL Server
> Management Studio, right click the database node, select
> Reports->Standard Reports->All transactions, the report said that there
> is no active transaction. 
> 
> However, through the step 1 and 2, there is still 1 transaction. 
> 
> What do you think about this scenario?

So your step 1 and 2 was to look in sys.dm_tran_session_transactions and
sys.dm_tran_active_transactions, and if I understand you correctly 
they display a transaction.

Using Profiler, I found that the report All Transactions in SSMS runs a
query of which the pertinent parts are:

   from    sys.dm_tran_database_transactions dt 
   ...
   inner join sys.dm_tran_active_transactions at  
       on (at.transaction_id = dt.transaction_id)   
   inner join sys.dm_tran_session_transactions st  
      on (st.transaction_id = dt.transaction_id) 
   ...
   where (dt.database_id = DB_ID()) and (st.is_user_transaction=1) 

That is, if this query lists no transactions, this means that your
transaction is not in sys.dm_tran_database_transactions, at least for
the database you look in. (And nor in tempdb, as the report also runs
the query i tempdb.) 

As I don't know your scenario, I cannot comment further, but I would
be intrigued if you can present a repro.

Maybe I be so curious to ask for what purpose you want to know whether
you have a transaction or not?


-- 
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Back to comp.databases.ms-sqlserver | Previous | Next — Previous in thread | Next in thread | Find similar

Thread

How to find not-committed transaction in SQL Server 2008 exactly? Peng Liu <liupengwyy@gmail.com> - 2013-04-27 00:11 -0700
  Re: How to find not-committed transaction in SQL Server 2008 exactly? Erland Sommarskog <esquel@sommarskog.se> - 2013-04-27 11:42 +0200
  Re: How to find not-committed transaction in SQL Server 2008 exactly? Peng Liu <liupengwyy@gmail.com> - 2013-04-27 19:57 -0700
    Re: How to find not-committed transaction in SQL Server 2008 exactly? Erland Sommarskog <esquel@sommarskog.se> - 2013-04-28 11:26 +0200
      Re: How to find not-committed transaction in SQL Server 2008 exactly? rja.carnegie@gmail.com - 2013-04-28 03:49 -0700
      Re: How to find not-committed transaction in SQL Server 2008 exactly? Peng Liu <liupengwyy@gmail.com> - 2013-05-05 20:35 -0700
        Re: How to find not-committed transaction in SQL Server 2008 exactly? Erland Sommarskog <esquel@sommarskog.se> - 2013-05-06 07:16 +0000
          Re: How to find not-committed transaction in SQL Server 2008 exactly? Peng Liu <liupengwyy@gmail.com> - 2013-05-06 18:40 -0700
            Re: How to find not-committed transaction in SQL Server 2008 exactly? Erland Sommarskog <esquel@sommarskog.se> - 2013-05-07 21:12 +0200
  Re: How to find not-committed transaction in SQL Server 2008 exactly? Peng Liu <liupengwyy@gmail.com> - 2013-04-28 00:52 -0700
  Re: How to find not-committed transaction in SQL Server 2008 exactly? Peng Liu <liupengwyy@gmail.com> - 2013-05-05 20:31 -0700
  Re: How to find not-committed transaction in SQL Server 2008 exactly? work4anvesh@gmail.com - 2015-08-26 10:47 -0700
  Re: How to find not-committed transaction in SQL Server 2008 exactly? work4anvesh@gmail.com - 2015-08-26 10:48 -0700
  Re: How to find not-committed transaction in SQL Server 2008 exactly? johnsonwelch2@gmail.com - 2016-04-14 01:46 -0700

csiph-web