How to check that there is transaction that is not yet committed in SQL Server 2005?

Question

Does anyone know the command to check if there is an un-committed transaction in SQL Server 2005?

Answer 1

use @@trancount or sys.dm_tran_active_transactions DMV in sql 2005, 2008

Answer 2

XACT_STATE() reports the transaction state of a session, indicating whether or not the session has an active transaction, and whether or not the transaction is capable of being committed. It returns three values:

• 1, The session has an active transaction. The session can perform any actions, including writing data and committing the transaction.
• 0, There is no transaction active for the session.
• -1, The session has an active transaction, but an error has occurred that has caused the transaction to be classified as an uncommittable transaction. The session cannot commit the transaction or roll back to a savepoint; it can only request a full rollback of the transaction. The session cannot perform any write operations until it rolls back the transaction. The session can only perform read operations until it rolls back the transaction. After the transaction has been rolled back, the session can perform both read and write operations and can begin a new transaction.

@@TRANCOUNT Returns the number of active transactions for the current connection.

• 0, not in a transaction
• 1, in a transaction
• n, in a nested transaction