Vlad Drumea doesn’t want to restart SQL Server:
This is a brief post containing a script you can use to delete extra tempdb data files and avoid the “file not empty” (Msg 5042) error.
I’ve occasionally ran into situations where an instance is configured with more than the recommended number of tempdb data files.
If you’re dealing with more than a couple of extra files it can get annoying, especially on a busy instance.
Read on for the script and how you can use it on busy and not-so-busy instances.