Getting notification when log truncation is delayed

Question

Is there any way to notify DBA proactively when transaction log truncation has been queued multiple times however for some reason (it could be an open transaction, replication, currently running full backup) failed to truncate the log.

Considering a scenario in which client application initiate a transaction using BEGIN TRAN and perform a huge update, it failed to acquire necessary locks during the set time (as another transaction was blocking) and it times out (in .net commandtimeout is set to default 30 seconds and no error handling in client application) and leave an open transaction on SQL server which in turn prevent log truncation. Now everytime log backup is run it can't truncate log because you have an open (hosed) transaction. I admit we can subscribe to log growth event in many ways but was wondering if there is something built in to the SQL server that notifies when log truncation has been queued multiple times but it failed to truncate it.

Answer 1

I think I rather use auto growth notification using notification event whenever transaction log grows to make sure it is cause by an open transaction.

Answer 2

I'd approach this from a different tack and monitor for long running transactions. You can query open transactions using the DMVs (sys.dm_tran_active_transactions should show it). Here's a query (borrowed and modified from Gail Shaw) that will show you currently running transactions:

SELECT 
    ec.connect_time,
    tat.transaction_begin_time,
    tat.transaction_type,
    tat.transaction_state,
    es.host_name,
    es.program_name,
    tst.session_id,
    es.login_name,
    est.text
FROM sys.dm_tran_session_transactions tst 
    INNER JOIN sys.dm_tran_active_transactions tat ON tst.transaction_id = tat.transaction_id
    INNER JOIN sys.dm_exec_connections ec ON tst.session_id = ec.session_id
    INNER JOIN sys.dm_exec_sessions es ON tst.session_id = es.session_id
    CROSS APPLY sys.dm_exec_sql_text(ec.most_recent_sql_handle) est

From here, I'd set up a polling job against the DMVs and alert me if a transaction is held open for longer than an acceptable period of time. What this amount of time is will be dependent on your environment.