1

My Logfile keeps auto grow even though approximately only half of the VLF is used, can anyone explain this?

See picture below. I truncated the logfile yesterday, this is the first load since and from my understanding the ones which have 0 as vlf sequence number haven't been used. But still the logfile auto grew, the one which have 150 in vlf_size_mb is auto grow. How come this happened?

enter image description here

picture below with not the same lsn, lsn last column.

I didn't try anything

Here is from the sys.trace_event as you can see the log did auto grow, does anyone know how this can happen without the VLFs beeing used?

enter image description here

Improve this question
1
  • What does SELECT name, log_reuse_wait_desc from sys.databases say for this database?
    – J.D.
    Nov 13 at 13:44

1 Answer 1

Reset to default
2

[...] from my understanding the ones which have 0 as vlf sequence number haven't been used.

You're correct.

But still the logfile auto grew [...]

The log won't grow, I highly doubt it automatically grew given the image in the OP which shows that the log reused some VLFs already and has not used the maximum size of the file.

One way to investigate is to look for autogrowths in the default trace which has event 93 for log autogrowth.

select trace_event_id, name FROM sys.trace_events where name like 'Log%grow%'

There is also the ability to run sys.dm_db_log_info to get the create lsn for the log, if it's all the same lsn then it did not grow.

Given the updated screenshots showing that auto grow did occur, the most likely explanation is due to log reservation as part of a large transaction.

All of the autogrowths happened when VLF 34627 was active and it looks as though it's still active. Given the parity change at VLF 34620 it's most likely that there was a large transaction running which required more space to be reserved and caused the log growth.

Improve this answer
2
  • Hi Sean, thanks for the answer! Do I understand you correct that SQL server estimated there would be a bigger transaction than it was and therefor reserved the extra space?
    – Marson
    Nov 17 at 12:45
  • When any transactions occur, part of the job of the transaction is to reserve log space so that the operations can undo themselves if needed. The combination of reserved + used space could have made the log grow. It still seems odd to me that it'd grow so much, but we don't really have any other data and without re-creating the logs it's not very possible to get an exact answer, hence my best guess here with the data available.
    – Sean Gallardy
    Nov 19 at 15:49

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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