Is there a way to list open transactions on SQL Server 2000 database?
For all databases query sys.sysprocesses
SELECT * FROM sys.sysprocesses WHERE open_tran = 1
For the current database use:
DBCC OPENTRAN
You can get all the information of active transaction by the help of below query
SELECTtrans.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 tasJOIN sys.dm_tran_session_transactions transON (trans.transaction_id=tas.transaction_id)LEFT OUTER JOIN sys.dm_tran_database_transactions tdsON (tas.transaction_id = tds.transaction_id )LEFT OUTER JOIN sys.databases AS DBsON tds.database_id = DBs.database_idLEFT OUTER JOIN sys.dm_exec_sessions AS ESesON trans.session_id = ESes.session_idWHERE ESes.session_id IS NOT NULL
and it will give below similar result
and you close that transaction by the help below KILL query by refering session id
KILL 77
DBCC OPENTRAN helps to identify active transactions that may be preventing log truncation. DBCC OPENTRAN displays information about the oldest active transaction and the oldest distributed and nondistributed replicated transactions, if any, within the transaction log of the specified database. Results are displayed only if there is an active transaction that exists in the log or if the database contains replication information.
An informational message is displayed if there are no active transactions in the log.