How to find the active transactions in VLF?
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 &gt; 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.