Long running open transactions in a SQL Server database

Query to return long running transactions and cumulative CPU time in MS for open transactions in a SQL Server Database

SELECT
            r.session_id
,           r.start_time
,           TotalElapsedTime_ms = r.total_elapsed_time
,           r.[status]
,           DatabaseName = DB_Name(r.database_id)
,           r.wait_type
,           r.last_wait_type
,           r.wait_resource
,           r.cpu_time
,           r.reads
,           r.writes
,           r.logical_reads
,           t.[text] AS [executing batch]
,           SUBSTRING(
                                                t.[text], r.statement_start_offset / 2, 
                                                (           CASE WHEN r.statement_end_offset = -1 THEN DATALENGTH (t.[text]) 
                                                                         ELSE r.statement_end_offset 
                                                            END - r.statement_start_offset ) / 2 
                                     ) AS [executing statement] 
,           p.query_plan
FROM
            sys.dm_exec_requests r
CROSS APPLY
            sys.dm_exec_sql_text(r.sql_handle) AS t
CROSS APPLY    
            sys.dm_exec_query_plan(r.plan_handle) AS p
ORDER BY 
            r.total_elapsed_time DESC;

A long running transaction keeps the transaction log active from the virtual log file containing the first log record of the transaction. Truncation cannot occur from that virtual log file onward. It may leads to LOG FULL errors and finally impacts the complete database operations. So proper monitoring of long running transactions is critical.

We can discover long running transactions with DMV “sys.dm_tran_database_transactions”. 

Space used

SELECT b.session_id 'Session ID',
       CAST(Db_name(a.database_id) AS VARCHAR(20)) 'Database Name',
       c.command,
       Substring(st.TEXT, ( c.statement_start_offset / 2 ) + 1,
       ( (
       CASE c.statement_end_offset
        WHEN -1 THEN Datalength(st.TEXT)
        ELSE c.statement_end_offset
       END 
       -
       c.statement_start_offset ) / 2 ) + 1)                                                             
       statement_text,
       Coalesce(Quotename(Db_name(st.dbid)) + N'.' + Quotename(
       Object_schema_name(st.objectid,
                st.dbid)) +
                N'.' + Quotename(Object_name(st.objectid, st.dbid)), '')    
       command_text,
       c.wait_type,
       c.wait_time,
       a.database_transaction_log_bytes_used / 1024.0 / 1024.0                 'MB used',
       a.database_transaction_log_bytes_used_system / 1024.0 / 1024.0          'MB used system',
       a.database_transaction_log_bytes_reserved / 1024.0 / 1024.0             'MB reserved',
       a.database_transaction_log_bytes_reserved_system / 1024.0 / 1024.0      'MB reserved system',
       a.database_transaction_log_record_count                           
       'Record count'
FROM   sys.dm_tran_database_transactions a
       JOIN sys.dm_tran_session_transactions b
         ON a.transaction_id = b.transaction_id
       JOIN sys.dm_exec_requests c
           CROSS APPLY sys.Dm_exec_sql_text(c.sql_handle) AS st
         ON b.session_id = c.session_id
ORDER  BY 'MB used' DESC

Transaction information is recorded in each database’s transaction log. However, long-running transactions can be a cause of consternation to the system administrator who is attempting to back up and prune the transaction log. Only the inactive portion of the log can be truncated during this operation. The inactive portion of the log is the pages containing log records for all completed transactions prior to the first log record of the oldest still-active transaction (see Figure 31.5). Even if completed transactions follow the first record of the oldest active transaction, they cannot be removed from the log until the oldest active transaction completes. This is because the log is pruned by clearing out entire pages of information prior to the oldest active transaction. Pages after this point cannot be cleared because they might contain records for the active transaction that would be needed in the event of a rollback or database recovery.

In addition to preventing the log from being pruned, long-running transactions can degrade concurrency by holding locks for an extended period of time, preventing other users from accessing the locked data.

To get information about the oldest active transaction in a database, you can use the DBCC OPENTRAN command. The syntax is as follows:

DBCC OPENTRAN ('MyDB01') 
GO

USE [MyDB01]
GO

DBCC OPENTRAN()

Transaction information for database 'MyDB01'.
 Oldest active transaction:
     SPID (server process ID): 60
     UID (user ID) : -1
     Name          : DELETE
     LSN           : (2684079:223192:1)
     Start time    : Oct  3 2021 11:47:11:943AM
     SID           : 0x01050000000000051500000013cbd8cb7b4bc6e1f6ea8c9e6a040000
 DBCC execution completed. If DBCC printed error messages, contact your system administrator.

DBCC OPENTRAN returns the spid of the process that initiated the transaction, the user ID, the name of the transaction (this is when naming transactions are helpful because they might help you identify the SQL code that initiated the transaction), the Log Sequence Number (LSN) of the page containing the initial BEGIN TRAN statement for the transaction, and finally, the time the transaction was started.

Note: If you kill the process, the database will be rolled back. This process will take a long time to roll back, almost as long as the time it takes to run the transaction.

Sources:

https://www.sqlservercentral.com/articles/monitoring-longest-running-transaction-using-sql-server-agent-alerts

http://etutorials.org/SQL/microsoft+sql+server+2000/Part+IV+Transact-SQL/Chapter+31.+Transaction+Management+and+the+Transaction+Log/Long-Running+Transactions/