Why would shrinking TEMPDB log lead to slowness?

Posted on

Question :

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?

Answer :

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?

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.

One more point to add to the two good existing answers.

Once you decide what the best size for tempdb is, you should manually set it to that size. If this is not done when SQL restarts monthly as the system is patched (assuming you do monthly patches) it will reset to the smaller size and will need to grow, giving the performance issues mentioned in the other answers.

USE [master]
ALTER DATABASE [tempdb] MODIFY FILE ( NAME = N'temp2', SIZE = 2560000KB )
ALTER DATABASE [tempdb] MODIFY FILE ( NAME = N'tempdev', SIZE = 2560000KB )

Also besure a reasonable auto growth setting is provided. It should be a value in MB, never use percentage.

When a file grows as a result of a transaction, that transaction is paused while the file is grown. Any other transactions using the file may also be affected. This is in addition to the strain that growing the file places on the file systems, although separating your files can help with this.

Log files, specifically must be grown and zeroed out before they can be used. You can enable instant file initialization for data files, however.

Ideally, you should let tempdb grow to the size it needs and leave it there, although if an errant transaction is what caused the growth it is acceptable to shrink it back to a normal size. But shrinking it to 0 was not the best action to take.

Not only for tempdb but also for any other db, shrinking to a size less than needed would cause slowness. You are not only making db engine more grow work, also increasing vlf amount. Look for VLF on google please.

Leave a Reply

Your email address will not be published. Required fields are marked *