是否可以查看在未提交的事务中运行了哪些查询？

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.

我的应用程序(使用带有SQL Server 2000的SQL Native Client的C ++)始终如一地进入挂起状态。我相信这是因为事务在某个表上的某个地方未被提交,并且由于打开的事务,该表上的SELECT查询被阻塞。

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?

不幸的是,我确实无法确定代码中挂起事务的位置。有没有办法让SQL Server指出在未提交的事务上运行了哪些查询?

1 个解决方案

#1

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

如果你有admin(sa)proviliges,你可以运行sp_Who或sp_Who2来显示所有服务器活动,通过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.

DBCC OPENTRAN(T-SQL)显示有关指定数据库中最早的活动事务和最早的分布式和非分布式复制事务(如果有)的信息。仅当存在活动事务或数据库包含复制信息时,才会显示结果。如果没有活动事务,则显示信息性消息。

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.

但是,当用户会话终止时,Sql Server应自动回滚任何打开的事务。

#1