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

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

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

Show key headers only | View raw

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, SQL_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 DBA script please visit this url:
http://www.dbrnd.com/category/script/

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