All we need is an easy explanation of the problem, so here it is.
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.
How to solve :
I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.
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.
Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂