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?

Moreau

Answer 1

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

Satirize

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

Associative

+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? – Hippocampus

@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. – Associative

XACT_STATE() shows transactions for your Session (also SELECT @@trancount and DBCC OPENTRAN ) For a problem with a transaction from another session or a program SELECT * FROM sys.sysprocesses WHERE open_tran = 1 Shows other sessions transactions along with the spid so you can kill it SELECT * FROM sys.dm_tran_active_transactions shows both user and system transactions sp_lock shows everything that is locked not just transactions - works well with SELECT * FROM sys.sysprocesses WHERE open_tran = 1 which tells you which process to investigate or kill. – Lona

Answer 3

run

DBCC OPENTRAN

Didactic

Answer 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..

Alkoran

Answer 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

Grijalva

A better answer. XACT_STATE() shows transactions for your Session (also SELECT @@trancount and DBCC OPENTRAN ) For a problem with a transaction from another session or a program SELECT * FROM sys.sysprocesses WHERE open_tran = 1 Shows other sessions transactions along with the spid so you can kill it SELECT * FROM sys.dm_tran_active_transactions shows both user and system transactions sp_lock shows everything that is locked not just transactions - works well with SELECT * FROM sys.sysprocesses WHERE open_tran = 1 which tells you which process to investigate or kill. – Lona

Answer 6

sp_who2 sp_lock

Snuggery

Answer 7

For transactions in your session

XACT_STATE()   
SELECT @@trancount
DBCC OPENTRAN 

For a problem with a transaction from another session or a program

SELECT * FROM sys.sysprocesses WHERE open_tran = 1

Shows other sessions transactions along with the spid so you can kill it

SELECT * FROM sys.dm_tran_active_transactions 

shows both user and system transactions but has less useful out put

sp_lock shows everything that is locked not just transactions - works well with SELECT * FROM sys.sysprocesses WHERE open_tran = 1 which tells you which process to investigate or kill.

So if you it a simple in session check to see the current transaction state then XACT_STATE()
SELECT @@trancount DBCC OPENTRAN

And for a proper transaction problem when you are trying to work out what is blocking things/access to a table etc, then sp_who2 and

SELECT * FROM sys.sysprocesses WHERE open_tran = 1

Lona