Determine witch transaction bloc log truncation

Michel PRIORI 21 Reputation points


Some application connections include IMPLICIT TRANSACTION OFF parameter.
Database recovery model is simple.
A transaction is never ended (lack of COMMIT) ; no blocking tree.

Demand :
How to check out opened transactions (in this case all sessions create a transaction) where there is something awaiting COMMIT and blocking log file truncation ?
Ideally: SPID to kill, client information (address, program, ...) and all sql instructions in this transaction, vlf list directly affected by this transaction (rows modified), vlf impacted consequently (vlf blocking tree)

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
11,609 questions
0 comments No comments
{count} votes

Accepted answer
  1. Erland Sommarskog 93,056 Reputation points

    The quick way to find the offender is to run


    in the database in question. But it will only give you the spid and when the transaction started.

    If you want to get an idea of what the process has been doing, my beta_lockinfo can help you, as it will list all locks the process is holding and some information about the process. But not all statements it has executed; that is nothing that SQL Server tracks by default.

    Note: beta_lockinfo is a free stored procedure that I share for the benefit of the community.

1 additional answer

Sort by: Most helpful
  1. Rahul Randive 5,656 Reputation points Microsoft Employee

    Hi @Michel PRIORI

    You can try below command.

    WITH cteBL (session_id, blocking_these) AS 
    (SELECT s.session_id, blocking_these = x.blocking_these FROM sys.dm_exec_sessions s 
    CROSS APPLY    (SELECT isnull(convert(varchar(6), er.session_id),'') + ', '  
                    FROM sys.dm_exec_requests as er
                    WHERE er.blocking_session_id = isnull(s.session_id ,0)
                    AND er.blocking_session_id <> 0
                    FOR XML PATH('') ) AS x (blocking_these)
    SELECT s.session_id, blocked_by = r.blocking_session_id, bl.blocking_these
    , batch_text = t.text, input_buffer = ib.event_info, * 
    FROM sys.dm_exec_sessions s 
    LEFT OUTER JOIN sys.dm_exec_requests r on r.session_id = s.session_id
    INNER JOIN cteBL as bl on s.session_id = bl.session_id
    OUTER APPLY sys.dm_exec_sql_text (r.sql_handle) t
    OUTER APPLY sys.dm_exec_input_buffer(s.session_id, NULL) AS ib
    WHERE blocking_these is not null or r.blocking_session_id > 0
    ORDER BY len(bl.blocking_these) desc, r.blocking_session_id desc, r.session_id;

    Which would give you result in below format. You will get all running spids and in the input_buffer column you can find the command which are running under spids. Also, culprit spid which block others sessions. User's image

    Thank you!

    0 comments No comments