How to find the active transactions in VLF?

Is there a way to find the T-SQL statement that is part of the active transaction that is blocking the reuse of the transaction log file VLFs?

I am performing tlog backups on every 15 minutes, but sometimes the following query returns ACTIVE_TRANSACTION or LOG_BACKUP (even such is made):

SELECT [name]
      ,[recovery_model_desc] 
      ,[log_reuse_wait_desc]
FROM [sys].[databases]

If I run DBCC Loginfo I can see that the firsts and the lasts rows have status 0.

LOG_REUSE_WAIT_DESC will show LOG_BACKUP any time there has been at least one transaction that has not been backed up (when the database is in Full Recovery mode).

You can see active sessions, requests, and transaction count for each session, by looking at the results of this query:

SELECT SessionID = s.session_id
    , HostName = s.host_name
    , ProgramName = s.program_name
    , SessionElapsedTime = s.total_elapsed_time
    , RequestCommand = r.command
    , DatabaseName = d.name
    , StatementExecuting = case when r.statement_end_offset > 0 THEN 
            SUBSTRING(t.text, r.statement_start_offset, r.statement_end_offset - 
            r.statement_start_offset) 
            ELSE t.text END
    , tst.open_transaction_count
FROM sys.dm_exec_sessions s
    LEFT JOIN sys.dm_exec_connections c ON s.session_id = c.session_id
    LEFT JOIN sys.dm_exec_requests r ON s.session_id = r.session_id
    LEFT JOIN sys.databases d ON r.database_id = d.database_id
    LEFT JOIN sys.dm_tran_session_transactions tst ON s.session_id = tst.session_id
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) t;

If I wrap that query in a transaction, you can see the open_transaction_count field shows 1, which is expected since I have no other activity ongoing on my Tablet.

StackExchange Database Administrators Q#76414, answer score: 5