Sql-server – Getting notification when log truncation is delayed

sql-server-2008sql-server-2008-r2sql-server-2012

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.

Best Answer

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.

Related Solutions

Sql-server – Transaction Log Truncation with Copy Only

I'll be the first to admit the documentation is not very clear on this. They state that you should be offloading your backups to the secondaries, but while most statements are made in a general sense, I think they really mean, specifically, log backups (and copy_only backups, if you have some need for those).

You should occasionally run full backups on the primary IMHO. The copy_only restriction is about full backups, not log backups, and only applies to the secondary AFAIK.

You will not be able to use shrinkfile against your current transaction logs because they are full of activity that has technically not been backed up. Once you take a full (non-copy_only) backup on the primary, then let one log backup run, you should be able to shrink the log file manually. Currently your log backups are working because the database is set to full, but they are continuing to grow because (presumably) you've never run a proper full backup on the primary.

This should be a one-time operation, and you shouldn't shrink them too small; you need to set your primary to be backing up regularly, and you need to accommodate for the largest set of activity that will occur between full or log backups. I won't go on and on about how shrinking files only so they can grow again is a futile exercise and guarantees poor performance, but I could. :-)

Sql-server – Identify cause of unusual transaction log growth

This SQL shows queries with most "logical_writes":

SELECT TOP 100
    st.text,
    execution_count,
    total_elapsed_time,
    total_worker_time,
    total_logical_reads,
    total_logical_writes,
    total_physical_reads,
    total_clr_time,
    creation_time, last_execution_time,
    pl.query_plan
FROM sys.dm_exec_query_stats ps with (NOLOCK)
    Cross Apply sys.dm_exec_sql_text(ps.sql_handle) st
    Cross Apply sys.dm_exec_query_plan(ps.plan_handle) pl
ORDER BY total_logical_writes desc
OPTION (RECOMPILE);

This T-SQL could help to find direction, but it is not really what you asked for, as logical write != write. and logical write != log write.

It could be very very interesting to find more complete answer to your question.

Best Answer

Related Solutions

Sql-server – Transaction Log Truncation with Copy Only

Sql-server – Identify cause of unusual transaction log growth

Related Question