Why would shrinking TEMPDB log lead to slowness?

I recently accidentally shrunk tempdb log to almost 0 after getting alerted that log drive filling up. I am told that it will lead to slowness. Can someone please explain why it will lead to slowness?

I'd ask the person who told you that, to at least hear why they think it will decrease performance.

One reason is that your TLOG isn't going to stay at 0. Since you shrunk it so small, I presume you have Auto Growth set. Depending on how you configured it, it will grow a set amount each time, or a percentage of it's size each time. Thus, each time your transaction log needs to grow, SQL Server has to do work, which you could notice regarding performance.

A primary reason is detailed in the first link below:


When the Auto Growth mechanism of the Transaction Log kicks in, SQL
Server always has to zero-initialize the new chunk that is added to
the end of the file. It doesn’t matter if your SQL Server instance is
running with the privilege Instant File Initialization, or not – the
Transaction Log is always zero initialized.

What makes more sense is setting a dedicated drive for this file, and setting the LOG file to the size of the drive. Why shrink to 0 when you know it's only going to grow again, leaving you in the same spot with it’s size, cause fragmentation, etc?

• Why Transaction Log Auto Growths are Degrading your Performance

• Percent or 1MB Growth in Use, Slow File Growths

• Microsoft's Considerations for AutoGrow

tempdb uses the simple recovery model and clears the log when ever a checkpoint is issued. This automatically happens when the log usages reaches 70% and in the case of tempdb, it doesn't force dirty tempdb pages to disk. See Paul Randall's post on this. VLF's are made inactive if possible during the checkpoint as well. When you shrink the log, the VLFs are removed.

• Paul Randal 8 Steps to better Log Throughput (see 7-8)

• Pinal Dave Reducing High VLF Counts

StackExchange Database Administrators Q#251034, answer score: 13