TempDB Reporting Incorrect File Size

ben himself's picture

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: