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?

Solution 1

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

Solution 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

Solution 3

run

DBCC OPENTRAN

Solution 4

SELECT
    trans.session_id AS [SESSION ID],
    ESes.host_name AS [HOST NAME],login_name AS [Login NAME],
    trans.transaction_id AS [TRANSACTION ID],
    tas.name AS [TRANSACTION NAME],tas.transaction_begin_time AS [TRANSACTION BEGIN TIME],
    tds.database_id AS [DATABASE ID],DBs.name AS [DATABASE NAME]
FROM sys.dm_tran_active_transactions tas
JOIN sys.dm_tran_session_transactions trans
ON (trans.transaction_id=tas.transaction_id)
LEFT OUTER JOIN sys.dm_tran_database_transactions tds
ON (tas.transaction_id = tds.transaction_id )
LEFT OUTER JOIN sys.databases AS DBs
ON tds.database_id = DBs.database_id
LEFT OUTER JOIN sys.dm_exec_sessions AS ESes
ON trans.session_id = ESes.session_id
WHERE ESes.session_id IS NOT NULL

--DB name will get only when a SELECT @@TRANCOUNT command execute.otherwise NULL

execute the above code ... Will give the session details in which the transaction occures..

Solution 5

To sum up, there are several methods:

1. SELECT @@trancount
2. DBCC OPENTRAN
3. SELECT XACT_STATE()
4. sp_lock
5. SELECT * FROM sys.dm_tran_active_transactions

View more solutions

Comments

[Khurram Ali]

+1 very well explain ..how to perform full rollback of transaction as you said The session cannot commit the transaction or roll back to a savepoint; it can only request a full rollback of the transaction.. . what do you mean full rollback?

[KM.]

@KhurramAli, "-1" means you can not use "commit" or "rollback YourSavePoint", but you must use "rollback" which basically sets @@transcount to zero rolling back every nested transaction.