Remove tempdb files

Microsoft’s recommendation for optimizing tempdb is to create several files, so as to parallelise the I/O threads and reduce contention on the allocation pages inside the files.

The baseline recommendation is to create as many files as the system has CPUs. That recommendation should not be followed to the letter. On a system with 32 cores you are not going to create 32 files: it brings nothing, and it makes managing space in tempdb harder.

Four or eight files are generally enough.

So if you have created too many files, how do you reduce the number? The usual approach is to empty the files, then drop them.

That is not so easy in tempdb. Erin Stellato blogged about it in 2017.

The following command, which empties a file so it can then be dropped, generally raises an error saying the file contains a work table, which prevents it from being emptied.

USE [tempdb];
GO
DBCC SHRINKFILE (logicalname, EMPTYFILE);
GO

Erin Stellato’s solution is to restart SQL Server in minimal configuration mode, with the -f option, to perform the removal. In production, that means downtime.

Andy Mallon proposes a solution that looks like it works — I have not tested it yet. See his blog post. The idea is to create a SQL Server Agent job that performs the operation at service startup: a job can be scheduled to run on startup, before SQL Server has had time to create the work tables in tempdb.