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.