如何检查 SQL Server 2014 中的活动事务？

How to check active transactions in SQL Server 2014?

提问by Jason Clark

I am using SQL Server 2014 and want to know how to check my active transactions?

我正在使用 SQL Server 2014 并且想知道如何检查我的活动事务？

回答by Tharif

1. Query with sys.sysprocesses

   SELECT * FROM sys.sysprocesses WHERE open_tran = 1
   

2. 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. An informational message is displayed if there are no active transactions in the log.

3. sys.dm_tran_active_transactions

1. 查询 sys.sysprocesses

   SELECT * FROM sys.sysprocesses WHERE open_tran = 1
   

2. DBCC OPENTRAN：帮助识别可能阻止日志截断的活动事务。DBCC OPENTRAN 显示有关指定数据库的事务日志中最早的活动事务以及最早的分布式和非分布式复制事务（如果有）的信息。仅当日志中存在活动事务或数据库包含复制信息时才会显示结果。如果日志中没有活动事务，则会显示一条信息性消息。

3. sys.dm_tran_active_transactions

Returns information about transactions for the instance of SQL Server. Syntax

返回有关 SQL Server 实例的事务的信息。 句法

Wondering about Transaction ?

想知道交易？

A transaction is a single unit of work. If a transaction is successful, all of the data modifications made during the transaction are committed and become a permanent part of the database.

事务是单个工作单元。如果事务成功，则事务期间所做的所有数据修改都将被提交并成为数据库的永久部分。

Find more at docs

在文档中查找更多信息

回答by Alisson Gomes

Translation the 3. query that Tharif comments.

3. 翻译 Tharif 评论的查询。

select transaction_id, name, transaction_begin_time
 ,case transaction_type 
    when 1 then '1 = Read/write transaction'
    when 2 then '2 = Read-only transaction'
    when 3 then '3 = System transaction'
    when 4 then '4 = Distributed transaction'
end as transaction_type 
,case transaction_state 
    when 0 then '0 = The transaction has not been completely initialized yet'
    when 1 then '1 = The transaction has been initialized but has not started'
    when 2 then '2 = The transaction is active'
    when 3 then '3 = The transaction has ended. This is used for read-only transactions'
    when 4 then '4 = The commit process has been initiated on the distributed transaction'
    when 5 then '5 = The transaction is in a prepared state and waiting resolution'
    when 6 then '6 = The transaction has been committed'
    when 7 then '7 = The transaction is being rolled back'
    when 8 then '8 = The transaction has been rolled back'
end as transaction_state
,case dtc_state 
    when 1 then '1 = ACTIVE'
    when 2 then '2 = PREPARED'
    when 3 then '3 = COMMITTED'
    when 4 then '4 = ABORTED'
    when 5 then '5 = RECOVERED'
end as dtc_state 
,transaction_status, transaction_status2,dtc_status, dtc_isolation_level, filestream_transaction_id
from sys.dm_tran_active_transactions

回答by Rinoy Ashokan

If you want to know more details about active sessions like session ID, Host Name,Login Name,Transaction ID,Transaction Name,Trnasaction Begin Time,Databse ID,Database Name you can use below sql query

如果您想了解有关活动会话的更多详细信息，例如会话 ID、主机名、登录名、交易 ID、交易名称、交易开始时间、数据库 ID、数据库名称，您可以使用下面的 sql 查询

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

and you will get the result something like

你会得到类似的结果

回答by Madhivanan

or use DBCC command

或使用 DBCC 命令

DBCC OPENTRAN