1

Need to understand below behavior of tempdb:-

Due to some poor running queries which caused Tempdb bloating, we had shrink and tried to adjust the sizes of each tempdb file:

Before issue:

no of TempDB data files 8 

Size of each TempDB data file 100 GB

During issue:

no of TempDB data files 8 Size of each TempDB data file 200 GB

Due to above we had entire disk of 1.6TB filled out, hence we shrinked and adjusted the file to 120 GB each thus allocating 960 GB to temp drive from that 1.6 TB and disabling autogrowth

Later after couple of days we see below

no of TempDB data files 8
Size of each TempDB data file 50 GB

I am not able to get above why tempdb on its own went to 50 GB each file and how even though we though of fixing its initial size to 120 GB each ?

Adding some info: - NO restart happened or anyone manually reducing the file size further to 50 GB. Autogrowth was also disabled since the last shrink change.

Improve this question
2
  • @AaronBertrand: I was reading Ben's comment on that connect link as "Just something to think about is that tempdb if you modify the size, then it will be the initial size of the database files when you start up SQL. If it auto grows from there it will return to the initial size. How would you handle that in the gui or in properties?" > i am thinking if this could be the case but not sure since autogrow was disabled. Checking from sentry logs if can find something as what you said makes more sense because tempdb is no magic :) – BeginnerDBA Mar 4 '19 at 20:10
  • 2
    Step back and think about what problem you're trying to solve. Are you trying to prevent tempdb from exceeding 1 TB? Are you trying to prevent the 600 GB of free space on that drive from being used? For how long and why? Shrinking those files down repeatedly doesn't gain you anything if you don't change the workload behavior that is making them grow in the first place, and freeing space to re-use it is pointless too. So instead of worrying about initial size and 50 gb vs. 120 gb, turn to the workload, and see if there is any tuning that can be done there to reduce tempdb usage in general. – Aaron Bertrand Mar 4 '19 at 21:12
3

Really, the only things that will cause your tempdb files to shrink are:

  1. Someone used the 'SHRINKFILE` command to manually resize the files
  2. The SQL Service was restarted (due to a reboot, or the service being restarted manually, etc) and the file reverted to it's configured "default size" (which can be found in sys.master_files - note that it's different from current / initial size reported in sys.database_files)

You can audit your system (using the default trace or the file growth reports in SSMS) to see when and why file shrink events happened. You can find a history of reboots in the system_health XE session.

A side note about this:

we shrinked and adjusted the file to 120 GB each thus allocating 960 GB to temp drive from that 1.6 TB and disabling autogrowth

Unless you are leaving room for tempdb's log file, you should really size the tempdb files to fill this dedicated drive. Since you have autogrowth turned off, it's simply wasted space at this point =)

Improve this answer

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.