0

Let's say that someone wants to follow sage advice and properly setup TempDb and TempDb's log. But let's also say that the chosen sizes are almost certainly too big/aggressive. E.g. pretend we've got some extremes where TempDb's log is filling up a 250GB drive while there are 8 equally-sized TempDb files filling up a 1TB drive.

Is there a way to tell from a query that the sizes are "too big" after the server's been running under "normal" load for say a week or month? I.e. how would one gather enough information to make a reasonable, educated guess that a 25GB log and total 120GB of TempDB files are sufficient?

Or is there no other way except to shrink them down to "almost certainly too small", turn on auto-growth, and hope performance doesn't suffer too much for a few weeks while they grow to "proper sizes"? ...'cause that sure would suck.

Improve this question
4
  • You can monitor the temps file usage, logging to a table and looking for the high water mark. However, most of the major monitoring vendors should be able to do this for you without reinventing the wheel. Do you have a commercial monitoring software that you could leverage?
    – AMtwo
    Sep 3, 2020 at 23:29
  • What are you going to do with the space you free up? Shrinking now so you can watch it grow later when the workload changes doesn’t gain you anything except that you can say you freed up that space for a little while.
    – Aaron Bertrand
    Sep 4, 2020 at 0:31
  • Sure, there are probably extreme examples where you’ve allocated 1 TB and the workload never uses tempdb at all and will never use it unpredictably. That’s not common, though, and like a lot of configuration decisions, I’d prefer consistency over exceptions. Disk space is cheap, growth events are expensive, don’t play tug-of-war with your drives.
    – Aaron Bertrand
    Sep 4, 2020 at 0:32
  • My concern is that wasting disk space affects VM backups and other copy/move operations as well as the ability to duplicate VM images for DR/testing. While it's "cheap", wasting it quickly complicates any of those tasks. But, at the end of the day, I simply want to "know" instead of speculate.
    – Granger
    Sep 4, 2020 at 22:19

1 Answer 1

Reset to default
1

Could you not simply run sp_spaceused, or a cut down version as you probably don't need both datasets, on tempdb regularly and log the results?

Obviously this will likely result in an underestimate, because it is unlikely that peak space use will be exactly on one of your samples, so you will need to inflate the maximum figures. Perhaps you could use this method to decide a good starting point for the "shrink and watch if it grows back a bit" experiment that you describe.

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.