Opened transactions - Sql Server

Script to show all opened transactions:

SELECT d.name as DatabaseName
, ses.host_name as [host_name]
, ses.login_time AS session_login_time
, ses.last_request_start_time
, ses.last_request_end_time
, ses.login_name
, ses.nt_user_name
, ses.STATUS
, tst.session_id as SPID
, tst.enlist_count AS nr_active_reqs_in_session
, tst.is_user_transaction
, CASE tst.is_user_transaction WHEN 1 THEN 'Transaction initiated by user request'
                               WHEN 0 THEN 'System transaction' END AS tran_status
, tst.is_local
, CASE tst.is_local WHEN 1 THEN 'Local transaction'
                    WHEN 0 THEN 'Distributed transaction' END AS is_local_description
, tat.NAME
, tat.transaction_begin_time
, DATEDIFF(SECOND, tat.transaction_begin_time, GETDATE()) AS Duration
, CASE tat.transaction_type WHEN 1 THEN 'Read/write transaction'
                            WHEN 2 THEN 'Read-only transaction'
                            WHEN 3 THEN 'System transaction'
                            WHEN 4 THEN 'Distributed transaction' END AS tran_type_description
, tat.transaction_state
, CASE tat.transaction_state WHEN 0 THEN 'The transaction has not been completely initialized yet'
                             WHEN 1 THEN 'The transaction has been initialized but has not started'
                             WHEN 2 THEN 'The transaction is active'
                             WHEN 3 THEN 'The transaction has ended. This is used for read-only transactions'
                             WHEN 4 THEN 'The commit process has been initiated on the distributed transaction'
                             WHEN 5 THEN 'The transaction is in a prepared state and waiting resolution'
                             WHEN 6 THEN 'The transaction has been committed'
                             WHEN 7 THEN 'The transaction is being rolled back'
                             WHEN 8 THEN 'The transaction has been rolled back' END AS transaction_state_description
, tat.dtc_state
, CASE tat.dtc_state WHEN 1 THEN 'ACTIVE'
                     WHEN 2 THEN 'PREPARED'
                     WHEN 3 THEN 'COMMITTED'
                     WHEN 4 THEN 'ABORTED'
                     WHEN 5 THEN 'RECOVERED' END AS dtc_state_description
, tst.is_enlisted
, tst.is_bound AS bound_status
, CASE tst.is_bound WHEN 1 THEN 'active via bound sessions'
                    WHEN 0 THEN 'not active via bound sessions' END AS bound_status_description
, est.[text] [StatementText]
, 'BEGIN TRY kill '+CAST(tst.session_id AS VARCHAR(5))+'; END TRY BEGIN CATCH END CATCH;' as SqlScriptToKillTransaction
FROM            sys.dm_exec_sessions                              AS ses
INNER JOIN      sys.dm_tran_session_transactions                  AS tst ON ses.session_id = tst.session_id
INNER JOIN      sys.dm_tran_active_transactions                   AS tat ON tst.transaction_id = tat.transaction_id
INNER JOIN      sys.dm_tran_database_transactions                 AS tdt ON tat.transaction_id = tdt.transaction_id
INNER JOIN      sys.databases                                     AS d   ON tdt.database_id = d.database_id
LEFT OUTER JOIN sys.dm_exec_requests                              AS er  ON ses.[session_id] = er.[session_id]
LEFT OUTER JOIN sys.dm_exec_connections                           AS ec  ON ec.[session_id] = ses.[session_id]
OUTER APPLY     sys.dm_exec_sql_text(ec.[most_recent_sql_handle]) AS est
WHERE d.database_id > 4

Ce script ferme les transactions que la durée dépasse à 60 minutes.

DECLARE @sql VARCHAR(MAX);
SET @sql = '';
SELECT @sql = @sql + 'BEGIN TRY kill '+CAST(tst.session_id AS VARCHAR(5))+'; END TRY BEGIN CATCH END CATCH;'
FROM       sys.dm_exec_sessions              AS ses
INNER JOIN sys.dm_tran_session_transactions  AS tst ON ses.session_id = tst.session_id
INNER JOIN sys.dm_tran_active_transactions   AS tat ON tst.transaction_id = tat.transaction_id
INNER JOIN sys.dm_tran_database_transactions AS tdt ON tat.transaction_id = tdt.transaction_id
INNER JOIN sys.databases                            ON tdt.database_id = sys.databases.database_id
WHERE sys.databases.database_id > 4 and DATEDIFF(SECOND, tat.transaction_begin_time, GETDATE()) > 3600
PRINT @sql;
EXEC(@sql);

Source:
https://www.experts-exchange.com/questions/26868182/how-to-check-transaction-id-belongs-to-which-spid.html
https://msdn.microsoft.com/en-us/library/ms182792.aspx