So what happens when you run a query to check TempDB file sizes and SQL tells you that each file is 101 MB, but when you look in the file system, they’re actually a 45 GB each?
Also, when you try to shrink the file, the shrink file dialog window shows free space as a negative number. According MSDN:
Once you restart the instance the space will be freed back to the OS.
Hint,don’t do that! Well, I mean, you can if you want, but if you’re on a production server, that’s not really an option.
All you really need to do is select “Release unused space” for each file and it will correct the issue.
I have read that shrinking tempdb comes with a risk of corruption of data, but I haven’t yet seen that happen. If you are having other issues then it’s time to play doctor, weighing the risk of a side-effect against the likelihood of improving the situation. As for me and my house, we release the unused space.
If you want the T-SQL for that, here it is:
