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.

  • Not being an admin myself, wouldn't it boil down to checking the sys.databases dmv on a periodic basis for type of 4 and then figuring out how long it's been like that? – billinkc Nov 29 '12 at 17:04
  • that is reactive, not proactive I know we can always look into log_reuse_wait_desc however considering you have 200 SQL instances hosting 1200 databases how would you do it? – SQL Learner Nov 29 '12 at 17:05
  • Again, not an admin so I'm just typing aloud. If polling of the dmv and looking for a trend over time would identify the trouble scenario, then I would think an approach would be to run the query against a server and log that to a dedicated monitoring instance. Apply that process across your list of servers you care about. Now we have the information over time. A different process then interrogates that data and identifies has trouble threshold been crossed and then alerts (however that works in your organization). Or, buy a tool that does that monitoring. – billinkc Nov 29 '12 at 17:17

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:

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.


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.

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.