How to Remove Secondary Data File from SQL Server Database

To remove the secondary data file from the database, you need to move the data from the file you want to remove to other files in the same filegroup. In the following example we will remove the tempdev2 file from the tempdb database.

First check the current data file names of the database:

USE TempDB
GO
EXEC sp_helpfile
GO

Result will be similar to this:

tempdb_sp_helpfile_file_added

tempdev 1 C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\tempdb.mdf PRIMARY 524288 KB Unlimited 262144 KB data only
templog 2 C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\templog.ldf NULL 131072 KB Unlimited 10% log only
tempdev2 3 D:\Data\tempdev2.ndf PRIMARY 524288 KB Unlimited 262144 KB data only

To move the data from the file you want to remove (in this example the logical name of the data file is tempdev2) to other files in the same filegroup, run the following code:

USE tempdb
GO
DBCC SHRINKFILE (tempdev2, EMPTYFILE);

dbcc_shrinkfile_emptyfile

To remove the tempdev2 file from the tempdb database:

ALTER DATABASE tempdb REMOVE FILE tempdev2

tempdev2_file_removed

You don't need to restart SQL Server Service for removing tempdb files.

If you try to remove the primary data or log file from a database, you will get the following error:

Msg 5020, Level 16, State 1, Line 1
The primary data or log file cannot be removed from a database.

primary_file_cannot_be_removed

To see how to Add Data Files to SQL Server tempdb Database, read related article:
Add Data Files to SQL Server tempdb Database