[sql] Is there a way to list open transactions on SQL Server 2000 database?

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.

This question is related to sql sql-server transactions sql-server-2000

The answer is


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

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

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


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


Examples related to sql

Passing multiple values for same variable in stored procedure SQL permissions for roles Generic XSLT Search and Replace template Access And/Or exclusions Pyspark: Filter dataframe based on multiple conditions Subtracting 1 day from a timestamp date PYODBC--Data source name not found and no default driver specified select rows in sql with latest date for each ID repeated multiple times ALTER TABLE DROP COLUMN failed because one or more objects access this column Create Local SQL Server database

Examples related to sql-server

Passing multiple values for same variable in stored procedure SQL permissions for roles Count the Number of Tables in a SQL Server Database Visual Studio 2017 does not have Business Intelligence Integration Services/Projects ALTER TABLE DROP COLUMN failed because one or more objects access this column Create Local SQL Server database How to create temp table using Create statement in SQL Server? SQL Query Where Date = Today Minus 7 Days How do I pass a list as a parameter in a stored procedure? SQL Server date format yyyymmdd

Examples related to transactions

sql try/catch rollback/commit - preventing erroneous commit after rollback Laravel: Using try...catch with DB::transaction() UnexpectedRollbackException: Transaction rolled back because it has been marked as rollback-only Transaction marked as rollback only: How do I find the cause Transaction isolation levels relation with locks on table Spring transaction REQUIRED vs REQUIRES_NEW : Rollback Transaction When to use SELECT ... FOR UPDATE? Correct use of transactions in SQL Server MySQL : transaction within a stored procedure issue ORA-00001: unique constraint violated coming in INSERT/UPDATE

Examples related to sql-server-2000

SQL get the last date time record INSERT INTO @TABLE EXEC @query with SQL Server 2000 How to get the first and last date of the current year? SQL select max(date) and corresponding value SQL where datetime column equals today's date? Is there a way to get a list of all current temporary tables in SQL Server? Grant execute permission for a user on all stored procedures in database? Is there a way to list open transactions on SQL Server 2000 database? Get the time of a datetime using T-SQL? select a value where it doesn't exist in another table