Unable to remove additional tempdb file

Question

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);

Answer 1

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?

There are limitations for use of the DBCC SHRINKDATABASE command on the tempdb database. The target size for data and log files cannot be smaller than the size that is specified when the database was created or smaller than the last size that was explicitly set by using a file-size-changing operation such as ALTER DATABASE that uses the MODIFY FILE option or the command. Another limitation of BCC SHRINKDATABASE is the calculation of the target_percentage parameter and its dependency on the current space that is used.

Try running:

dbcc shrinkdatabase (tempddev, '70') 

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:

USE [tempdb]
GO
DBCC SHRINKFILE (N'tempdev2', EMPTYFILE)
GO

USE [tempdb]
GO
ALTER DATABASE [tempdb] REMOVE FILE [tempdev2]
GO

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:

ALTER DATABASE tempdb ADD FILE (NAME='tempdev2', FILENAME='M:\SQLData**tempdb3**.ndf', Size=30GB);

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

DBCC FREEPROCCACHE

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:

select * from tempdb.sys.all_objects
where is_ms_shipped = 0;

This will let you see your size of the tempdb files.

USE [tempdb]
SELECT
   [name]
   ,CONVERT(NUMERIC(10,2),ROUND([size]/128.,2))                                 AS [Size]
   ,CONVERT(NUMERIC(10,2),ROUND(FILEPROPERTY([name],'SpaceUsed')/128.,2))            AS [Used]
   ,CONVERT(NUMERIC(10,2),ROUND(([size]-FILEPROPERTY([name],'SpaceUsed'))/128.,2))      AS [Unused]
FROM [sys].[database_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.