How can I tell if TempDb is too big? 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 &quot;too big&quot; after the server's been running under &quot;normal&quot; 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 &quot;almost certainly too small&quot;, turn on auto-growth, and hope performance doesn't suffer too much for a few weeks while they grow to &quot;proper sizes&quot;? ...'cause that sure would suck.
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 &quot;shrink and watch if it grows back a bit&quot; experiment that you describe.