Introduction

Sometimes a transaction log can fill, causing client code to fail. Knowing what’s using the transaction log can be useful in both helping identify the cause of the problem and fixing it. The transaction log exists so that the current transaction can be rolled back if necessary, additionally, backing up the transaction log allows point in time restores.

This article presents a utility that will show you what SQL statements are using the transaction logs: both in terms of how much log space is used, and how long the transaction has been active. It will report on all the transaction logs of all the databases on a SQL Server instance.

What SQL Statements Are Currently Using The Transaction Logs Utility

The SQL code used in this utility is given in Listing 1 below.

The main body of the code relates to linking together 5 Dynamic Management Views (DMVs), and 1 Dynamic Management Function (DMF). The DMV sys.dm_tran_session_transactions returns correlation information for associated transactions and sessions. This DMV is joined to the DMV sys.dm_tran_active_transactions, which returns information about transactions for the instance of SQL Server. This DMV is joined to the DMV sys.dm_tran_database_transactions, which returns information about transactions at the database level. This DMV is joined to the DMV sys.dm_exec_sessions, which returns information about authenticated sessions on SQL Server. This DMV is joined to the DMV sys.dm_exec_requests, which returns information about each request that is currently executing within SQL Server. The DMV sys.dm_exec_requests is joined to the DMF sys.dm_exec_sql_text, which returns the underlying SQL text, via the supplied sql_handle. This can be seen in the below code block. Please see Books Online (BOL) for further details of the DMVs/DMF and columns involved. 

FROM sys.dm_tran_session_transactions AS tst
       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 tst.transaction_id = tdt.transaction_id
       INNER JOIN sys.dm_exec_sessions es
              ON tst.session_id = es.session_id
       INNER JOIN sys.dm_exec_requests er
              ON tst.session_id = er.session_id
       CROSS APPLY sys.dm_exec_sql_text(er.sql_handle) TXT

Running the utility on my SQL Server gives the results given in Figure 1. 

Figure 1 Output from the ‘What SQL statements are currently using the transaction logs’ utility.

The output shows each active transaction in each log. For each line, it shows:

• how long the transaction has been running
• the amount of log space used
• the state of the transaction
• the current specific piece of SQL code running
• the routine (stored procedure or batch) that contains the currently running SQL
• other interesting information (user, host name, spid, database name, etc) that is helpful 

The output is sorted by transaction duration, but it might also be useful to sort it by log space used.

Note: a given spid may have transactions in several databases.

Discussion

This utility allows you to see what SQL statements are currently included in the transaction logs, in terms of both log space used and transaction duration. This should prove useful in identifying SQL statements that are causing the transaction logs to fill. It will also help in identifying long running transactions.

Knowing what SQL code is causing the problem means corrective action can be taken to prevent the log filling. This might include using smaller transactions (e.g. commit after xx rows processed) and ensuring the transaction is not left open (i.e. issue a commit or rollback).

In recent months I’ve been involved with several production problems where the client application has failed because the transaction log has run out of space. This utility has helped me identify the culprit code, and corrections have then been made to prevent the log from filling.

Further Work

It is possible to create a simple monitor to report on what SQL is using the transaction logs just before they fill. I’ve created some monitor code, which checks every 30 seconds how full any of the non-system database logs are, when they are over a given threshold (e.g. 90%), it shows the result of this utility i.e. what’s using the transaction logs just before they fill. If there’s interest, I might write this up as a follow-up article.

Credits

Ian Stirk has been working in IT as a developer, designer, and architect since 1987. He holds the following qualifications: M.Sc., MCSD.NET, MCDBA, and SCJP. He is a freelance consultant specializing in SQL Server in London England. He can be contacted at Ian_Stirk@yahoo.com.

Code

-- Purpose: Report active transactions by space or duration.
-- Author: I. Stirk.
-- Do not lock anything, and do not get held up by any locks.
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
-- What SQL statements are currently using the transaction logs?
SELECT tst.session_id
  , es.original_login_name
  , DB_NAME(tdt.database_id) AS DatabaseName
  , DATEDIFF(SECOND, tat.transaction_begin_time, GETDATE()) AS [TransDuration(s)]
  , tdt.database_transaction_log_record_count AS SpaceUsed
  , 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'
      WHEN 4 THEN 'The commit process has been initiated on the distributed tran'
      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'
      ELSE 'Unknown'
  END AS TransactionState
  , SUBSTRING(TXT.text, ( er.statement_start_offset / 2 ) + 1,
       ( ( CASE WHEN er.statement_end_offset = -1
                     THEN LEN(CONVERT(NVARCHAR(MAX), TXT.text)) * 2
                     ELSE er.statement_end_offset
              END - er.statement_start_offset ) / 2 ) + 1) AS CurrentQuery
  , TXT.text AS ParentQuery
  , es.host_name
  , 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'
              ELSE 'Unknown'
  END AS TransactionType
  , tat.transaction_begin_time AS StartTime
FROM sys.dm_tran_session_transactions AS tst
       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 tst.transaction_id = tdt.transaction_id
       INNER JOIN sys.dm_exec_sessions es
              ON tst.session_id = es.session_id
       INNER JOIN sys.dm_exec_requests er
              ON tst.session_id = er.session_id
       CROSS APPLY sys.dm_exec_sql_text(er.sql_handle) TXT
--ORDER BY tdt.database_transaction_log_record_count DESC -- log space size.
ORDER BY [TransDuration(s)] DESC -- transaction duration.

Listing 1