3

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.

migrated from stackoverflow.com Sep 11 '12 at 7:49

This question came from our site for professional and enthusiast programmers.

  • You say there is no change in the application but what about any jobs that run on the server? – HLGEM Sep 10 '12 at 20:06
2

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.

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.