Who/What is causing transaction log to grow?

[JtheRipper]

Hi there,

I have a problem in where our transaction log is growing abnormally big and fast. We do a transaction log backup every hour. The size of the log backup grew from approx 200MB every hour to 3.5GB every hour! I tried to use SQL Profiler to see maybe if I can capture the user doing the DML causing the log to grow, but no such luck. I don't know if I maybe chose the wrong criteria to trace the transactions against the database as there are so many to choose from.

Does anyone have any ideas on how I can try to trace the user/app causing this amount of redo to be generated.

Thanks,
J.

 

[nigelrivett]

Presumably it will be a long running query so try looking at locks. Anything with a lot of locks is a candidate.



======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

 

[JtheRipper]

Hi,

Thanks for the quick response. I had a look at locks, but there are no deadlocks. There are approx 70 normal locks (shared) on the database and all different spid's.

 

[JtheRipper]

Hi,

I found the problem. There was a change made to an application that did an update to the database without a where clause, updating a 15GB table!

Anyway, why did I not see the transaction in the SQL Profiler?

Thanks,
J.