The log file stays pretty small (~500MB) until about 11PM, and at that point it begins growing until no disk space is available. The log file reached it's maximum size at 12:39AM, so the growth took place over approximately an hour and a half time frame. The drive that houses the logs only has about 40GB of total space available, so the log file can only grow to that 40GB. That should be plenty though as the data is fairly static and only about 50GB total. Here are a few more additional notes:
- DB recovery model is full
- Transaction log backups are taken every 15 minutes
- A defrag job is called at 11PM but looking at the history it only runs for a few minutes max
How can I pinpoint the exact cause of this? It has to be a large transaction that is open but how can I determine that?
Best Answer
Best thing to do is to collect what caused the log file to grow by setting up an alert.
You can use sp_whoisactive to run and log into the table (as DennisT suggested) or dmv's like
sys.dm_tran_active_transactions
andsys.dm_tran_database_transactions
More reference :