12

I have read many blogs here and there about how to configure tempdb data files but i didnt found any information regarding the tempdb log file.

Here's the strategy that I'm presently using with my tempdb:

  • I have used the recommendations of Paul Randal on how to split my tempdb data files
  • I have set the size of my tempdb data files to their maximum and disabled autogrowth. For example, i have 100gb of free disk space and set the size of my 8 tempdb data files to 10gb each. This prevent fragmentation on the disk as recommended by Brent Ozar and also I have 20gb free for my log file.

But like I said, nobody is talking about the tempdb log file. What should I do with it? On my setup, this file is at the same place as the tempdb data files. What is the size and the autogrowth value that I should use with the tempdb log file?

Improve this question
9
  • 4
    There is no magic answer for this, except that I would not disable autogrowth for tempdb data or log unless you absolutely want the system to come to a screeching halt should autogrow be needed. I would never disable autogrow on any file, because no matter how well you plan, there are always abnormal situations.
    – Aaron Bertrand
    Apr 18, 2013 at 14:21
  • 2
    @AaronBertrand: enable autogrowth on a full sized tempdb will not help. They are already at their maximum they can be on the drive. There's no more disk space that they can use anyway. This is why i have disabled the autogrowth.
    – Alexandre Jobin
    Apr 18, 2013 at 14:27
  • 2
    Then it doesn't matter if it's disabled or not, does it? If an autogrow is required, it's going to fail either way. If you leave it enabled, that's one less thing you have to change when you realize that maybe you need to move it to a bigger drive.
    – Aaron Bertrand
    Apr 18, 2013 at 14:31
  • 1
    P.S. 8 x 20 > 100
    – Aaron Bertrand
    Apr 18, 2013 at 14:32
  • 1
    Splitting your files isn't necessarily "a good setup". It depends on your hardware configuration; which you have not supplied.
    – Paul
    Jul 19, 2013 at 9:40

3 Answers 3

Reset to default
5

It really depends on how much data is going to flow through the transaction log. Look at how big the log gets today. You need to configure the log to be at least that size when SQL starts up. For most of my clients they end up with a 3-4 Gig transaction log for the tempdb, which contains just a few VLFs and everything works nice and smoothly.

Improve this answer
0

I was always told to store log files on a separate physical/disk array if your hardware can write simultaneously to data and log at the same time to improve performance. I would guess the limitation that spawned your 20G tempdb limit might define an answer for size. For autogrowth, that might depend on the transactions you are running as well as free space available and other resources on disk. I typically pick 1/6th of the initial size arbitrarily.

Improve this answer
0

Aaron is right configuring TempDB is dependent on lot of variables such as whether or not you plan on using snapshot isolation etc. Here is a older SQL 2005 white paper from MS/TN that might help you out since a good bit of it still applies. Specifically have a look at the "Space required for tempdb logging" so you can see what kinds transactions are logged in the tempdb log so you can set things up accordingly. It is likely going to be the sort of thing that you will have to monitor and tweak over time.

As far as authogrowth for tempdb I have reluctantly disabled this on a reporting server in the past and the behavior I have experienced was that it caused the long running transaction to abort and roll back which instantly freed up the log space but that might depend on the type of transaction which in my case is was massive sorts and join operations (poorly written report queries). I agree with Aaron and would recommend avoiding this if possible especially in high transaction volume situations.

Improve this answer

Your Answer

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

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