2

An external company has a job set up which shrinks the log with truncate only - I checked the default trace and for example last night DBCC SHRINKLOG WITH TRUNCATEONLY happened 300 times. If I also check autogrowth events I see that the log auto grew by 2GB only once - I thought there job may be causing the log to expand and contract hundreds of times each night, causing performance issues, but it seems their job is causing the log to expand once, then they shrink it back. I know this is still bad, but not as bad as I hoped!! (So I could present this overwhelming evidence to them and get them to stop the shrinks).

Are these SHRINKLOG WITH TRUNCATEONLY events, even if they have nothing to truncate, still causing performance issues?

Thanks

Improve this question
3
  • I actually thought about this a little more and there is potentially constant expansion and contraction - the log just isnt allowed to grow enough to require an autogrowth event - so all night it is growing and shrinking in tiny increments, which is indeed terrible!!
    – jack
    Jan 14, 2020 at 9:00
  • A shrink log operation is not an issue itself. The issue was that all processes (which had to write to the log ) were waiting the whole time the log file was growing. And it means all transactions were stopped for significant time.
    – Denis Rubashkin
    Jan 14, 2020 at 9:00
  • Thanks Denis - does this apply even to growths inside the log file, e.g. it seems like the log file in this case is 3GB and they want to prevent it getting bigger, so its constantly shrunk (multiple times per second the autoshrinks run) so it must be say 2.5GB full, then it fills to 3.1 and the 0.1 is lopped off with truncateonly, so on and so forth (its SHRINKLOG, 3000, TRUNCATEONLY)
    – jack
    Jan 14, 2020 at 9:09

1 Answer 1

Reset to default
1

Are these SHRINKLOG WITH TRUNCATEONLY events, even if they have nothing to truncate, still causing performance issues?

How could this not cause performance issues? Even if there is nothing to shrink it takes work to figure that out.

I would ask them why they are using SHRINKLOG WITH TRUNCATEONLY I expect they don't know why or even that they are doing this. Even if they know why, there is almost no chance it is for a good reason. If they have a good reason, you should post it, I doubt anyone has ever seen a good reason for this.

If the system is in FULL Recovery, the ONLY time you can shrink free space is after the log backup has run. In the very extreme case when there is a good reason (almost never) do it once after the log backup.

If the system is in SIMPLE recovery, the VLF files can only empty when when all the inflight work has completed. And you can only truncate the very last VLF, and then the one before that, etc. Truncating more then once an hour or maybe at the end of the job is the only way you might actually shrink something. See an in depth view in this related Why are virtual log files not always allocated in order?

Depending on what version of SQL you are running and the current size of the log file, a 2GB grow will create between 1 and 16 VLF files. The smallest VLF in this scenario is 125MB, so at the very best the smallest amount a shrink could remove is 125MB, see more in this post

Even if they have a really good reason for shrinking (again I doubt they do) there is not a good reason to do it as often as they are.

Improve this answer

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.