Is there a way to list open transactions in a SQL Server 2000 database?

Question

Does anyone know of any way to list open transactions in a SQL Server 2000 database?

I am aware that I can query the sys.dm_tran_session_transactions in SQL 2005 database versions (and later), however this is not possible in SQL 2000.

Answer 1

For all databases, sys.sysprocesses request

 SELECT * FROM sys.sysprocesses WHERE open_tran = 1 

For the current database, use:

 DBCC OPENTRAN 

Answer 2

DBCC OPENTRAN helps identify active transactions that may prevent log breaks . DBCC OPENTRAN displays information about the oldest active transaction and the oldest distributed and unallocated replicated transactions, if any, in the transaction log of the specified database. Results are displayed only if an active transaction exists in the log or the database contains replication information.

An informational message is displayed if there are no active transactions in the log.

DBCC OPENTRAN

Answer 3

You can get all the information about an active transaction with the following query

 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 

and he will give below a similar result

and you close this transaction using the help below KILL , specifying the session ID

 KILL 77