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:
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);
To remove the tempdev2 file from the tempdb database:
ALTER DATABASE tempdb REMOVE FILE tempdev2
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.
To see how to Add Data Files to SQL Server tempdb Database, read related article:
Add Data Files to SQL Server tempdb Database