In our production DB, we only have 1 tempdb file and it had ballooned to 180GB.
Alter Database tempdb modify file (name='tempdev', size=30gb);
to resize the primary file, and then ran additional:
ALTER DATABASE tempdb ADD FILE (NAME='tempdev2', FILENAME='M:\SQLData\tempdb2.ndf', Size=30GB);
to add additional tempdb files. I ran into a drive space error, as the primary did not shrink to 30gb and the operation was halted mid-way.
I am now left with one additional tempdb file that is 30GB while the primary is 180GB and I am unable to run get rid of it.
I have tried running
DBCC SHRINKFILE('tempdev2', EMPTYFILE)
but I get an error:
Could not locate file 'tempdev2' for database 'tempdb' in sys.database_files.
The file either does not exist, or was dropped.
I feel this error is because the operation was not able to complete successfully. (Do I have corruption?)
And when I try to run
ALTER DATABASE tempdb REMOVE FILE tempdev2;
I get the following error:
The file "M:\SQLData\tempdb2.ndf" has been modified in the system catalog. The new path will be used the next time the database is started.
Msg 5042, Level 16, State 1, Line 35
The file 'tempdev2' cannot be removed because it is not empty.
Edit: I was able to resolve the 'file not found' filename corruption that i was receiving when trying to run the EMPTYFILE Shrink. I used the File ID instead of the logical name.
DBCC SHRINKFILE(3,EMPTYFILE); -- File ID instead of logical name.
Another method I found that worked was renaming the file.
ALTER DATABASE tempdb MODIFY FILE (NAME =tempdev2, NEWNAME =tempdev3);