Reputation: 1
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 maintenance 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 SQL Server to let it create tempdb files (to then drop them), as it would simply require too much storage.
Environment is running on SQL Server 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.
Upvotes: 0
Views: 116