Is it possible to see what queries have been run in an uncommitted transaction?

Question

My application (C++ using SQL Native Client with SQL Server 2000) is consistently finding its way into a hanging state. I believe this is because a transaction is left uncommitted someplace on a table and a SELECT query on that table, as a result of the open transaction, is blocking.

Unfortunately, I'm really having trouble determining where the hanging transaction might be in my code. Is there any way to get SQL Server to indicate what queries have been run on an uncommitted transaction?

Answer 1

if you have admin (sa) proviliges, you can run sp_Who, or sp_Who2 to show all server activity, by Spid, Run

Exec sp_Who2 [SpidNumber]

to just see the one session you are interested in...

To directly see open transactions, run

DBCC OPENTRAN (T-SQL) Displays information about the oldest active transaction and the oldest distributed and nondistributed replicated transactions, if any, within the specified database. Results are displayed only if there is an active transaction or if 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]
        ]

Sql Server should, however, automatically rollback any open transaction when a user session is terminated.