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

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

Upvotes: 45

Answers (7)

Ronald Duncan

Reputation: 149

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

Upvotes: 0

Mark Varnas

Reputation: 741

To sum up, there are several methods:

SELECT @@trancount
DBCC OPENTRAN
SELECT XACT_STATE()
sp_lock
SELECT * FROM sys.dm_tran_active_transactions

Upvotes: 6

Itachi

Reputation: 71

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

Upvotes: 7

DForck42

Reputation: 20327

sp_who2 sp_lock

Upvotes: 0

KM.

Reputation: 103579

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.