On Saturday, April 27, 2013 at 12:11:56 AM UTC-7, Peng Liu wrote:   
   > My C++ code accesses SQL Server by ODBC driver 2.x, and calls   
   > SQLSetConnectAttr( pMSSQLHandles->hDbc, SQL_ATTR_AUTOCOMMIT, S   
   L_AUTOCOMMIT_OFF, 0 );   
   > to set the  transaction to implicit transaction. I also call SQLEndTran() to   
   commit the transaction.   
   >    
   > After my application starts， in SQL Server 2008 Managment Studio, run   
   "DBCC OPENTRAN", no active open transaction is reported. And "select   
   @@TRANCOUNT" return 0.    
   >    
   > However, if run   
   > select * from sys.dm_tran_session_transactions   
   > we can see there is one record， and we can also find the transaction   
   corresponding to this record in sys.dm_tran_active_transactions.   
   >    
   > Below SQL script will return 1 records.   
   > select s.dbid, s.spid,s.loginame, s.status,d.name,s.last_batch   
   datediff(minute,s.last_batch,GETDATE()) as IdleTimeInMin,    
   >     s.open_tran,t.text   
   > from sys.sysprocesses s   
   > join sys.databases d on d.database_id = s.dbid   
   > cross apply sys.dm_exec_sql_text (s.sql_handle) t   
   > where d.name = 'XXX' and s.loginame <> 'sa'   
   > order by s.last_batch   
   >    
   > I am curious that which method is the exact one to get all the not-committed   
   transactions.   
   > What is the meaning of the records in dm_tran_active_transactions,   
   dm_tran_session_transactions and dm_tran_database_transactions?   
   > What is difference between Open transaction and active transaction?   
   >    
   > Thanks in advance.   
      
   I am using this script   
   SELECT    
        tdt.transaction_id   
        ,tst.session_id   
        ,tdt.database_transaction_begin_time   
        ,CASE tdt.database_transaction_type   
           WHEN 1 THEN 'Read/write transaction'   
   	WHEN 2 THEN 'Read only transaction'   
   	WHEN 3 THEN 'System transaction'   
         END transaction_type   
        ,CASE tdt.database_transaction_state   
   	WHEN 1 THEN 'Transaction not initialized'   
   	WHEN 3 THEN 'Transaction has not generated by any log'   
   	WHEN 4 THEN 'Transaction has generated by log'   
   	WHEN 5 THEN 'Transaction Prepared'   
   	WHEN 10 THEN 'Transaction Committed'   
   	WHEN 11 THEN 'Transaction Rolled back'   
   	WHEN 12 THEN 'Transaction committed and log generated'   
        END transaction_state        
   FROM sys.dm_tran_database_transactions tdt   
   INNER JOIN sys.dm_tran_session_transactions tst   
     ON tst.transaction_id = tdt.transaction_id   
      
   For more SQL Server   
      
   --- SoupGate-Win32 v1.05   
    * Origin: you cannot sedate... all the things you hate (1:229/2)