Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 2 down vote favorite
2

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

Previously the transaction log used to grow to 10-20Gb every 2-3 days and 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 increate in growth rate.

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

share|improve this question
Why are you not backing up the transaction log at least hourly so that you don't get this kind of growth in it at all? You should not need to shrink the transaction log if you are doing proper backups. – HLGEM Sep 10 '12 at 20:05
I agree backing up or shrinking the file will release the disk space. I just think that is just a band aid over the actual problem though. The application is doing more of something; thereby using more resource than before. – u07ch Sep 11 '12 at 6:27
@Kev Please feel free to move it to the dba stack site. – u07ch Sep 11 '12 at 6:29
that's why I put that is in a comment vice an answer. – HLGEM Sep 11 '12 at 21:33

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

5 Answers

up vote 1 down vote accepted

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.

share|improve this answer
Thanks for this; came at it in a similar way using the code from the executions report. Will put a bounty on the question if there isn't another answer when i am able. – u07ch Sep 5 '12 at 14:16
up vote 0 down vote

You may use trial version of ApexSQL Log (nice UI) and by switching on and off corresponding filters see exactly how many transaction of each transaction types you have in your T-Log.

share|improve this answer
up vote 0 down vote

You say there is no change in the application but what about any jobs that run on the server?

share|improve this answer
The change log says nothing has been amended. Spend a lot of time looking at the growth of log files; but never given them much mind as to whats going on inside them. The abnormal behaviour has spurred me to investigage. – u07ch Sep 11 '12 at 21:27
If you are doing data imports, has the size of the file(s) increased significantly? – HLGEM Sep 11 '12 at 21:31
up vote 0 down vote

If no changes has been introduced in your app., and no new jobs are running... Is your DB server been upgraded recently?

In this other DBA thread you can find other question that might be related to yours.

In that case it was solved by following the instructions provided on the MSDN here.

share|improve this answer
up vote -1 down vote

If you constantly have to shrink, that means your size is not correct, and your backup frequency needs to increase as well.

share|improve this answer
Im trying to be proactive; I can manage the size of the file and 20gb of transaction log isnt really the issue. What I am interested in figuring out; is what transactions are causing the abnormal pattern in the growth of the file - the change log says nothing has changed in the business but the application is using the disks differently, – u07ch Sep 11 '12 at 21:25
-1 since you don't give any hints as to why the database is sized incorrectly, or how to size it correctly. You should provide some ability for the original poster to fix his problem. – Max Vernon Sep 26 '12 at 5:22

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.