How to check active transactions in SQL Server 2014?

Question

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

Answer 1

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

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

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

Answer 2

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

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