How to Identify Active Transactions and Running SQL in SQL Server

When a SQL Server database becomes slow, blocked, or starts showing signs of transaction log growth, one of the first things a DBA wants to know is:

Which transaction is running, who started it, and what SQL is it executing?

That is exactly what this script helps answer.

By combining several SQL Server Dynamic Management Views (DMVs), this query gives a deeper view into active transactions, the sessions behind them, the requests currently executing, and the actual SQL text being run.

The Script

select *
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

What this script does

This query links together transaction-level and session-level information so you can see:

  • which sessions have active transactions
  • what request is currently running
  • which database transaction is involved
  • details about the SQL Server session
  • the SQL text associated with the request

In simple terms, it is a transaction troubleshooting query.

It is especially useful when you are investigating:

  • long-running transactions
  • blocking issues
  • deadlock precursors
  • excessive transaction log growth
  • stuck or uncommitted sessions
  • performance bottlenecks caused by open transactions
Breaking down the script

1. sys.dm_tran_session_transactions AS tst

This DMV maps a session to a transaction.

A session in SQL Server is basically a connection. A transaction is the unit of work being processed. This DMV helps answer:

Which session owns which transaction?

Example fields you may get from this DMV include session ID and transaction ID.

This is the starting point of the query.

2. sys.dm_tran_active_transactions AS tat

INNER JOIN sys.dm_tran_active_transactions AS tat
    ON tst.transaction_id = tat.transaction_id

This DMV provides details about transactions that are currently active in SQL Server.

It gives information such as:

  • transaction name
  • transaction type
  • transaction state
  • begin time

By joining it with tst, you can move from just knowing that a session has a transaction to understanding the state and nature of that transaction.

This is useful for checking whether a transaction is active, deferred, being committed, or rolling back.

3. sys.dm_tran_database_transactions AS tdt

INNER JOIN sys.dm_tran_database_transactions AS tdt
    ON tst.transaction_id = tdt.transaction_id

This DMV adds database-level transaction details.

It can show things such as:

  • database transaction state
  • log record count
  • log bytes reserved or used
  • begin LSN and other logging-related details

This part is particularly useful when troubleshooting:

  • transaction log growth
  • heavy write activity
  • long-running transactions consuming log space

If your transaction log is filling up unexpectedly, this DMV often helps explain why.

4. sys.dm_exec_sessions es

INNER JOIN sys.dm_exec_sessions es
    ON tst.session_id = es.session_id

This join brings in the session information.

That means you can see who or what opened the connection, including useful details like:

  • login name
  • host name
  • program name
  • session status
  • client interface
  • login time

This helps answer practical questions such as:

  • Which user started the transaction?
  • Which application is responsible?
  • Did it come from SSMS, an application server, or a background job?

For DBAs, this is often where the investigation becomes actionable.

5. sys.dm_exec_requests er

INNER JOIN sys.dm_exec_requests er
    ON tst.session_id = er.session_id

This DMV shows requests currently executing in SQL Server.

A request is the actual command being processed at the moment. This join helps expose:

  • command type
  • wait type
  • blocking session ID
  • CPU time
  • elapsed time
  • reads and writes
  • status

This is extremely useful for live performance troubleshooting because it shows what the session is doing right now.

For example, if the session is blocked, waiting on I/O, or running a large update, sys.dm_exec_requests helps surface that.

6. CROSS APPLY sys.dm_exec_sql_text(er.sql_handle) TXT

CROSS APPLY sys.dm_exec_sql_text(er.sql_handle) TXT

This is the part that gives you the actual SQL text.

Without this, you might know the session and transaction details, but not the exact query being executed. By using the SQL handle from the active request, SQL Server returns the text of the statement or batch.

This allows you to see the real command, such as:

  • UPDATE ...
  • DELETE ...
  • INSERT ...
  • EXEC procedure_name
  • long-running ad hoc SQL

This is one of the most valuable pieces of the entire script because it connects transaction activity to the exact SQL responsible.

Why this script is powerful

On its own, each DMV tells only part of the story. This script is powerful because it combines them into one view.

It connects:

  • the transaction
  • the session
  • the request
  • the database transaction impact
  • the SQL text

That gives you a more complete troubleshooting picture.

Instead of checking multiple views one by one, you can run one query and quickly identify:

  • which session owns the transaction
  • which user or application started it
  • what SQL is running
  • whether it is affecting the transaction log
  • whether it may be blocking other sessions
Typical use cases

Investigating blocking

If one session is blocking others, this query can help identify the active transaction behind it and the SQL text responsible.

Finding long-running transactions

Open transactions that remain uncommitted for a long time can cause locking, blocking, and log growth. This script helps locate them.

Troubleshooting transaction log growth

If the transaction log keeps growing and not truncating, one common reason is a long-running active transaction. This query helps expose that relationship.

Identifying application issues

Sometimes the problem is not SQL Server itself, but an application that opens a transaction and forgets to commit or rollback. With session and SQL text details, you can trace the issue back to the source.

Real-time production troubleshooting

During incidents, this kind of query helps DBAs quickly answer the most important questions without wasting time jumping between tools.

A few practical notes

1. It only shows active requests

Because the query joins to sys.dm_exec_requests, it focuses on sessions with a currently executing request. If a session has an open transaction but is idle at that moment, it may not appear here.

That is important to remember. Some sleeping sessions with uncommitted transactions may require a slightly different query.

2. SELECT * returns a lot of columns

Using select * is useful for ad hoc troubleshooting, but the result can be noisy. In real-world monitoring, it is often better to select only the columns you care about.

3. Permissions are required

To query these DMVs, you usually need elevated permissions such as VIEW SERVER STATE or the equivalent permissions supported by your SQL Server version.

A cleaner version for day-to-day use

For practical monitoring, this version is easier to read:

select
    tst.session_id,
    tst.transaction_id,
    tat.name as transaction_name,
    tat.transaction_begin_time,
    tat.transaction_type,
    tat.transaction_state,
    DB_NAME(tdt.database_id) as database_name,
    es.login_name,
    es.host_name,
    es.program_name,
    er.status,
    er.command,
    er.blocking_session_id,
    er.wait_type,
    er.cpu_time,
    er.total_elapsed_time,
    txt.text as sql_text
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;

This version keeps the same logic but makes the result much easier to interpret.

Conclusion

This script is a handy SQL Server troubleshooting tool for DBAs, consultants, and support teams. It helps you connect the dots between active transactions, sessions, requests, and the SQL being executed.

When you are dealing with blocking, performance issues, or unexplained log growth, this kind of DMV query can save a lot of time. Instead of guessing which session is responsible, you can see the evidence directly from SQL Server.