4

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.

5

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;

enter image description here

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.

enter image description here

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.