11

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?

13

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.

  • Thank you for your response. I understand that it will cause performance issue trying to Zero initialize the new VLFs when auto-growth happens.But will it cause any issue if there were any DML/DDL operations that were running at that time when I shrunk the log? Will it cause issue if any transactions were trying to be rolled back? Does it shrink the inactive VLFs or removes them and releases the space to the hard drive? Just a lot of questions running through my curious mind. – user193020 Oct 15 at 16:13
  • I added a bit more @user193020. Regarding DML/DDL operations and transactions that are trying to be committed or rolled back, I'm not sure i understand your question. SQL Server is ACID so your transaction is preserved if you are wondering if you will "lose" it. – scsimon Oct 15 at 17:24
6

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]
GO
ALTER DATABASE [tempdb] MODIFY FILE ( NAME = N'temp2', SIZE = 2560000KB )
GO
ALTER DATABASE [tempdb] MODIFY FILE ( NAME = N'tempdev', SIZE = 2560000KB )
GO

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

5

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.

-2

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.

  • Why are you suggesting google for VLF? There are a couple hundred posts here about it. – James Jenkins Oct 15 at 10:14
  • I understand that it will cause performance issue trying to Zero initialize the new VLFs when auto-growth happens.But will it cause any issue if there were any DML/DDL operations that were running at that time when I shrunk the log? Will it cause issue if any transactions were trying to be rolled back? Does it shrink the inactive VLFs or removes them and releases the space to the hard drive? Just a lot of questions running through my curious mind. – user193020 Oct 15 at 16:54
  • James calm down buddy, i wrote that answer from mobile I was not able to deliver any links. And for your questions, user193020, her are my answers. **But will it cause any issue if there were any DML/DDL operations that were running at that time when I shrunk the log? "no, the transactions which are already in the log file are not touched, that part of log will not be shrunk, though." *Will it cause issue if any transactions were trying to be rolled back? "as I said above, no" **Does it shrink the inactive VLFs or removes them and releases the space to the hard drive? "yes" – Mehmet Oct 23 at 6:43

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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