Identify cause of unusual transaction log growth

Question

I have an application running on SQL Server 2008 (it's a cluster but it hasn't failed over.)

Previously, the transaction log used to grow to 10-20GB every 2-3 days. We would shrink it back (after backup) to let it fill up again. Over the last few weeks, the log has been growing at 20GB a day; we don't believe we have changed the application code, so are wondering what process is causing the increase in growth rate.

What is a good strategy to identify transactions causing the largest increase in transaction log size?

Backing up or shrinking the file will release the disk space. I just think that is just a band-aid over the actual problem. The application is doing more of something; thereby using more resource than before.

Answer 1

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.