In our production DB, we only have 1 tempdb file and it had ballooned to 180GB.
I ran:
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.
The sys.database_files looks like this
Results from tempdb.sys.all_objects
Sys.database_files
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);
Best Answer
So your current situation is a full drive where you have two TempDB's that you want to modify the old one's size and the newly created one is having difficulties being removed?
Try running:
This should set your file to ~54GB which should be a safe size considering I am assuming your size or model was 30GB. Adjust the % accordingly.
Microsoft - How to shrink tempdb
Now for the second file, give this a try:
If the issue persists, you'll have to restart SQL Server to remove the file as TempDB is in use.
Microsoft Forum - File in use
SQL Authority - Shrink and remove Tempdb file
One last note, you typed this:
Your screenshot shows tempdb2, make sure your accurate with all the names and double check them. Just in case there is a mistype and that is causing your headaches.
There are some cases where running
can help alleviate the pain and allow you to shrink the file.
Ozar - When TempDB won't shrink.
If you are still having issues, see what is out in your tempdb:
This will let you see your size of the tempdb files.
From your post we see that you have files used in TempDB and there is space free. SQLSunday by Daniel Hutmacher has a good link on shrinking your DB with additional methods that were similar to Ozar, I would recommend to give this a try as well with caution.
There is a post by Mike Good - SQL Central on shrinking your TempDB by adjusting the snapshot level of TempDB with a rollback period to kill any connections that may have lingered on your db.