Sql-server – Understanding the TempDB growth

sql serversql-server-2012tempdb

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.

Best Answer

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 =)

Related Question