Opened Transactions - Sql Server

 1
 2SELECT d.name as DatabaseName
 3, ses.host_name as [host_name]
 4, ses.login_time AS session_login_time
 5, ses.last_request_start_time
 6, ses.last_request_end_time
 7, ses.login_name
 8, ses.nt_user_name
 9, ses.STATUS
10, tst.session_id as SPID
11, tst.enlist_count AS nr_active_reqs_in_session
12, tst.is_user_transaction
13, CASE tst.is_user_transaction WHEN 1 THEN 'Transaction initiated by user request'
14                               WHEN 0 THEN 'System transaction' END AS tran_status
15, tst.is_local
16, CASE tst.is_local WHEN 1 THEN 'Local transaction'
17                    WHEN 0 THEN 'Distributed transaction' END AS is_local_description
18, tat.NAME
19, tat.transaction_begin_time
20, DATEDIFF(SECOND, tat.transaction_begin_time, GETDATE()) AS Duration
21, CASE tat.transaction_type WHEN 1 THEN 'Read/write transaction'
22                            WHEN 2 THEN 'Read-only transaction'
23                            WHEN 3 THEN 'System transaction'
24                            WHEN 4 THEN 'Distributed transaction' END AS tran_type_description
25, tat.transaction_state
26, CASE tat.transaction_state WHEN 0 THEN 'The transaction has not been completely initialized yet'
27                             WHEN 1 THEN 'The transaction has been initialized but has not started'
28                             WHEN 2 THEN 'The transaction is active'
29                             WHEN 3 THEN 'The transaction has ended. This is used for read-only transactions'
30                             WHEN 4 THEN 'The commit process has been initiated on the distributed transaction'
31                             WHEN 5 THEN 'The transaction is in a prepared state and waiting resolution'
32                             WHEN 6 THEN 'The transaction has been committed'
33                             WHEN 7 THEN 'The transaction is being rolled back'
34                             WHEN 8 THEN 'The transaction has been rolled back' END AS transaction_state_description
35, tat.dtc_state
36, CASE tat.dtc_state WHEN 1 THEN 'ACTIVE'
37                     WHEN 2 THEN 'PREPARED'
38                     WHEN 3 THEN 'COMMITTED'
39                     WHEN 4 THEN 'ABORTED'
40                     WHEN 5 THEN 'RECOVERED' END AS dtc_state_description
41, tst.is_enlisted
42, tst.is_bound AS bound_status
43, CASE tst.is_bound WHEN 1 THEN 'active via bound sessions'
44                    WHEN 0 THEN 'not active via bound sessions' END AS bound_status_description
45, est.[text] [StatementText]
46, 'BEGIN TRY kill '+CAST(tst.session_id AS VARCHAR(5))+'; END TRY BEGIN CATCH END CATCH;' as SqlScriptToKillTransaction
47FROM            sys.dm_exec_sessions                              AS ses
48INNER JOIN      sys.dm_tran_session_transactions                  AS tst ON ses.session_id = tst.session_id
49INNER JOIN      sys.dm_tran_active_transactions                   AS tat ON tst.transaction_id = tat.transaction_id
50INNER JOIN      sys.dm_tran_database_transactions                 AS tdt ON tat.transaction_id = tdt.transaction_id
51INNER JOIN      sys.databases                                     AS d   ON tdt.database_id = d.database_id
52LEFT OUTER JOIN sys.dm_exec_requests                              AS er  ON ses.[session_id] = er.[session_id]
53LEFT OUTER JOIN sys.dm_exec_connections                           AS ec  ON ec.[session_id] = ses.[session_id]
54OUTER APPLY     sys.dm_exec_sql_text(ec.[most_recent_sql_handle]) AS est
55WHERE d.database_id > 4

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