如何检查 SQL Server 2005 中是否存在尚未提交的事务？

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

提问by pang

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

有谁知道检查 SQL Server 2005 中是否存在未提交事务的命令？

回答by Mladen Prajdic

use @@trancountor sys.dm_tran_active_transactionsDMV in sql 2005, 2008

在 sql 2005、2008 中使用@@trancount或sys.dm_tran_active_transactionsDMV

回答by KM.

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:

XACT_STATE()报告会话的事务状态，指示会话是否有活动事务，以及事务是否能够被提交。它返回三个值：

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

• 1、会话有一个活跃的事务。会话可以执行任何操作，包括写入数据和提交事务。
• 0，会话没有活动的事务。
• -1，会话有一个活动的事务，但是发生了错误，导致该事务被归类为不可提交的事务。会话无法提交事务或回滚到保存点；它只能请求完全回滚事务。会话在回滚事务之前不能执行任何写操作。会话只能执行读操作，直到它回滚事务。事务回滚后，会话可以执行读写操作，并可以开始新的事务。

@@TRANCOUNTReturns the number of active transactions for the current connection.

@@TRANCOUNT返回当前连接的活动事务数。

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

• 0，不在交易中
• 1、在一次交易中
• n、在嵌套事务中

回答by Mitch Wheat

run

跑

DBCC OPENTRAN

回答by Itachi

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

--DB 名称只会在 SELECT @@TRANCOUNT 命令执行时获得。否则为 NULL

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

执行上面的代码......会给出交易发生的会话细节......

回答by Mark Varnas

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

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

回答by DForck42

sp_who2 sp_lock

sp_who2 sp_lock