有人知道在SQL Server 2005中检查是否存在未提交事务的命令吗?
有人知道在SQL Server 2005中检查是否存在未提交事务的命令吗?
XACT_STATE() 用于报告一个会话的事务状态,指示该会话是否有活动事务,以及事务是否能够被提交。它返回三个值:
@@TRANCOUNT 返回当前连接的活动事务数。
运行
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
--仅在执行SELECT @@TRANCOUNT命令时,才会获取DB名称。否则为NULL
执行上述代码...将提供事务发生的会话详细信息。
总之,有以下几种方法:
sp_who2 sp_lock