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?

Toile

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

Ray

When you call BEGIN TRANSACTION twice, open_tran = 2. The query should be SELECT * FROM sys.sysprocesses WHERE open_tran > 0. – Irrepealable

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

Nudity

are there sys.dm_tran_active_transactions not in sys.dm_tran_session_transactions ? – Genitourinary

Answer 3

Translation the 3. query that Tharif comments.

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

Swarth

Answer 4

The Most Usefull method is;

DBCC opentran()

When You check, you will get below message;

Oldest active transaction: SPID (server process ID): 190 UID (user ID) : -1 Name : implicit_transaction LSN : (500549:37333:1) Start time : Dec 4 2021 10:36:21:673AM

And if you run DBCC opentran several times and you always get same server process ID then system then A transaction is stuck in the database.

Therefore, it is necessary to first look at the SPID detail with the code below and then kill that SPID process.

exec sp_who2 190
exec sp_lock 190


KILL 190

Turnage

Answer 5

or use DBCC command

DBCC OPENTRAN

Fiddlededee