There is a myth that modifications to tempdb do not take effect until you restart the instance, understandably people have read the message that will be printed during many changes. But with careful planning the message and the restarts can be avoided and many changes can instead be made to take effect immediately.

Recently I came across a server where database integrity checks were causing application disconnects in combination with sudden and massive spikes in latency on one disk. After looking at the configuration I could see 16 cores, MAXDOP 0, and 12x tempdb data files configured on the same spindle.

My interpretation was that MAXDOP would normally be configured in this scenario as 4, and that by over-parallelising and hitting so many tempdb data files the random IO was killing the server.

While it would have been possible to force a query hint to prevent this, I also take the stance that when a server is running on an out-of-the-box defaults configuration (or knee jerk 12x tempdb data file configuration) and is experiencing issues, we should align it to best practices first before digging deeper.

But it was a production server and while we could make changes in the maintenance window we couldn't afford to take it completely offline, and usually when modifying tempdb files you'll be notified that the changes have been written to the system catalog and will take effect on the next restart.

After some experimentation I've documented areas where it is possible to do some changes without a restart - including adding and removing files. Here's a demo configuration out of the box:

Use tempdb

Select	name, 
		type_desc,
		physical_name
From	sys.database_files df

It's possible to add and remove a data file without a restart… on an inactive system.

Alter Database tempdb 
Add File (Name = 'tempdev_2', Filename = 'C:\Program Files\Microsoft SQL Server\MSSQL12.SQL2014\MSSQL\DATA\tempdb_2.ndf')

Alter Database tempdb 
Remove File [tempdev_2] 

The file 'tempdev_2' has been removed.

But on a system which is in use things are not so easy. I'll demonstrate with two sessions.

-- Session 1

; With Cte As (
	Select	1 As Something
	Union	All
	Select	Something + 1
	From	Cte
	Where	Something < 1000000
	)
Select	*
Into	#OccupyTempdb
From	Cte
Option	(Maxrecursion 0)

-- Session 2 (while Session 1 is running)

Alter Database tempdb 
Add File (Name = 'tempdev_2', Filename = 'C:\Program Files\Microsoft SQL Server\MSSQL12.SQL2014\MSSQL\DATA\tempdb_2.ndf')

Waitfor	Delay '00:00:05'

Alter Database tempdb 
Remove File [tempdev_2] 

Msg 5042, Level 16, State 1, Line 19
The file 'tempdev_2' cannot be removed because it is not empty.

Even after you drop #OccupyTempdb from Session 1 you still cannot remove the file you've just added. You need to shrink and empty the file first.

Dbcc Shrinkfile('tempdev_2', Emptyfile)

Alter Database tempdb 
Remove File [tempdev_2] 


(1 row(s) affected)
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
The file 'tempdev_2' has been removed.

It's possible to get an idea of how full the files are with this:

Select	size / 128.0 As SizeInMB,
		Fileproperty(name, 'SpaceUsed') / 128.0 As UsedInMB,
		Cast((Fileproperty(name, 'SpaceUsed') * 100.0) / size As Int) As PercentFull, 
		name
From	sys.database_files sd

It is possible though that if the system is busy enough, you won't be able to shrink and empty the file. This is trickier to reproduce but I was able to flub it (your results may vary).

-- Session 2

Create Database Test_Snapshot
Go
Use Test_Snapshot
Go

; With Cte As (
    Select  1 As Something
    Union   All
    Select  Something + 1
    From    Cte
    Where   Something < 1000000
    )
Select  *
Into    Occupy_Tempdb
From    Cte
Option  (Maxrecursion 0)
Go

While 	1 = 1
Begin
	Insert	Occupy_Tempdb
	Select	Something
	From	Occupy_Tempdb
	Order By Newid()
End

-- Session 3

Use	 Test_Snapshot
Go

While 1 = 1
Begin
	Insert	Occupy_Tempdb
	Select	Something
	From	Occupy_Tempdb
	Order By Newid()
End

-- Session 1

Alter Database tempdb 
Add File (Name = 'tempdev_2', Filename = 'C:\Program Files\Microsoft SQL Server\MSSQL12.SQL2014\MSSQL\DATA\tempdb_2.ndf')

Waitfor Delay '00:00:05'

Dbcc Shrinkfile('tempdev_2', Emptyfile)

Alter Database tempdb 
Remove File [tempdev_2]

DBCC SHRINKFILE: Page 3:127 could not be moved because it is a work table page.
DbId   FileId      CurrentSize MinimumSize UsedPages   EstimatedPages
------ ----------- ----------- ----------- ----------- --------------
2      3           128         128         120         120

(1 row(s) affected)

DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Msg 5042, Level 16, State 1, Line 43
The file 'tempdev_2' cannot be removed because it is not empty.

Note that that error can't be caught in a Try Catch block.

The main problem with this is that it's not reliable. I have seen the Emptyfile fail and then the Remove File succeed and notify that it has removed the file from the system catalog; this is the worst case scenario because it still shows the file properly in sys.database_files and it's still in use but it can't be shrunk, emptied, or properly removed until you restart the instance.

Unfortunately I was unable to reproduce it for this post. I did find that the @Error variable does get set when Emptyfile fails though, so you should be safe to wrap your Remove File in a quick test to make sure you have the best chance of removing the file without a restart:

Dbcc Shrinkfile('tempdev_2', Emptyfile)
If 	@@Error = 0
Begin
	Alter Database tempdb Remove File [tempdev_2]
End