Some time ago I noticed that one of our servers has old tempdb ndf files still in sys.master_files.
Of course sys.databases or sys.database_files are actual.
Problem is that after an instance restart new ndf files for tempdb are not recreating, because the old ndf file that is in sys.master_files is blocking them.
You can actually spot it in the SQL log as the server starts:
Create file encountered operating system error 3(The system cannot find the path specified.) while attempting to open or create the physical file …

Yes it cannot open the path, because it no longer exists.
Some time ago there was a maintanence windows and databases, temps and logs where moved to different disks, so the original disk doesn’t even exist anymore.

The question is, how can I update / remove or purge old and unused rows from sys.master_files as it doesn’t allow ad hoc queries and its a system vide view.

Also, there is no way to add a disk and restart MSSQL to let it create tempdb files (to then drop them), as it would simply require too much storage.

Environment is running on SQL STD 2016.

I have tried to enable allow updates on test environment but that still doesn’t let me run queries on sys.master_files.
I’ve tried to drop databases that are not existing, but of course you cannot drop what doesn’t exist anymore…
I’ve tried restarting SQL, but that doesn’t update the view.
I was thinking about replacing master database with a template, but I’m too afraid of losing Agent jobs, users, etc.