107

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

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

James Wiseman
  • 28,429
  • 17
  • 89
  • 155

4 Answers4

176

For all databases query sys.sysprocesses

SELECT * FROM sys.sysprocesses WHERE open_tran = 1

For the current database use:

DBCC OPENTRAN
Jibin Balachandran
  • 3,211
  • 1
  • 18
  • 36
gbn
  • 394,550
  • 75
  • 549
  • 647
42

You can get all the information of active transaction by the help of below 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 it will give below similar result enter image description here

and you close that transaction by the help below KILL query by refering session id

KILL 77
Rinoy Ashokan
  • 1,167
  • 14
  • 13
  • 1
    Wow, this one is very nice, i like the amount of data provided, the only con is that you need to be administrator in order to run it, DBCC OPENTRAN needs fewer permissions. But still... very good – Yogurtu Sep 05 '17 at 12:35
  • Very useful, thank you! If it answered the OP's question (SQL 2000), it should have been the accepted answer. Have an upvote... – Reversed Engineer Sep 13 '18 at 09:06
25

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.

DBCC OPENTRAN

Tharif
  • 13,172
  • 9
  • 51
  • 73
codingbadger
  • 38,990
  • 13
  • 90
  • 103
1

Use this because whenever transaction open more than one transaction then below will work SELECT * FROM sys.sysprocesses WHERE open_tran <> 0

kanti
  • 11
  • 4