Search code examples
sql-servershrinktempdb

SQL Server - Shrink DB still large file tempdb_mssql_2

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.

Solution

  • So to put an answer here for anyone else that stumbles across this:

    1. Shrink DB is a bad idea, it's better to increase resources.
    2. If you still need to shrink all the tempdbs then you can do so by first finding the them with the following script:

    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