I have a server that is getting full, due to some temp table processing.
We found that our tempdb file was at 33.8 GB
and there was a another file at 33.8 GB called tempdb_mssql_2
Now then, we ran the following:
USE [tempdb]
GO
DBCC SHRINKFILE (N'templog' , 0)
GO
DBCC SHRINKFILE (N'tempdev' , 0)
GO
The tempdb did shrink as expected, but the other file tempdb_mssql_2
is still 33.8 GB
I believe this file is a secondary data file created by SQL, but don't know how to clear it down.
we tried this:
DBCC SHRINKFILE (N'tempdb_mssql_2' ,0)
GO
But this gave us this error:
Msg 8985, Level 16, State 1, Line 8 Could not locate file 'tempdb_mssql_2' for database 'tempdb' in sys.database_files. The file either does not exist, or was dropped.
So to put an answer here for anyone else that stumbles across this:
SELECT name FROM tempdb.sys.database_files WHERE name NOT IN ('templog','tempdev');
In my case this returned a single file called temp2
From that you will get the name of the tempDB's and can shrink them
USE [tempdb]
GO
DBCC SHRINKFILE (N'temp2' , 0)
GO