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

    From work4anvesh@gmail.com@21:1/5 to Peng Liu on Wed Aug 26 10:47:26 2015
    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

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)
  • From work4anvesh@gmail.com@21:1/5 to Peng Liu on Wed Aug 26 10:48:12 2015
    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/

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)
  • From johnsonwelch2@gmail.com@21:1/5 to All on Thu Apr 14 01:46:53 2016
    check active transactions in SQL Server http://www.sqlserverlogexplorer.com/check-active-transactions-in-sql-server/

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)