Is it possible to see what queries were executed in an uncommitted transaction?
My application (C ++ using SQL Native Client with SQL Server 2000) consistently finds its way in a hung state. I believe this is due to the fact that the transaction remains uncommitted in some table, and the SELECT query on this table is blocked as a result of an open transaction.
Unfortunately I am actually having trouble figuring out where the dangling transaction might be in my code. Is there a way to get SQL Server to indicate which queries were executed on an uncommitted transaction?
source to share
if you have admin (sa) proviliges you can run sp_Who or sp_Who2 to show all server activity, Spid, Run
Exec sp_Who2 [SpidNumber]
to see only the session that interests you ...
To directly see open transactions, run
DBCC OPENTRAN (T-SQL) Displays information about the oldest active transaction and the oldest distributed and unallocated replicated transactions, if any, in the specified database. Results are displayed only if there is an active transaction or the database contains replication information. An informational message is displayed if there are no active transactions.
Syntax
DBCC OPENTRAN
( {'database_name' | database_id}
) [ WITH TABLERESULTS [, NO_INFOMSGS]
]
The Sql server should, however, automatically rollback any open transaction when the user session ends.
source to share