Determine witch transaction bloc log truncation

Question

Hello,

Situation:
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)

Answer 1

The quick way to find the offender is to run

DBCC OPENTRAN

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.

Answer 2

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.

Thank you!