是否有方法列出 SQLServer2000数据库上打开的事务?

有人知道列出 SQLServer2000数据库上打开的事务的方法吗?

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.

198161 次浏览

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.

如果日志中没有活动事务,则显示信息性消息。

DBCC OPENTRAN

对于所有数据库,请查询 sys.sysprocess

SELECT * FROM sys.sysprocesses WHERE open_tran = 1

供当前数据库使用:

DBCC OPENTRAN

您可以通过以下查询获得所有活动事务的信息

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

它将给出下面类似的结果enter image description here

通过 KILL查询下面的帮助引用会话 ID 来关闭该事务

KILL 77

使用此选项是因为当事务打开多个事务时,下面的选项将起作用

SELECT * FROM sys.sysprocesses WHERE open_tran <> 0