I was trying to delete a data file but I encountered the error below.
Error: 5042 - The file 'MyFileName' cannot be removed because it is not empty.
I executed the script below to empty the file.
DBCC SHRINKFILE(First_Data_File,EMPTYFILE )
After the file has been emptied, I was able to remove the data file.
ALTER DATABASE DBNAME REMOVE FILE First_File;
EMPTYFILE Migrates all data from the specified file to other files in the same filegroup. In other words, EmptyFile will migrate the data from the specified file to other files in the same filegroup. Emptyfile assures you that no new data will be added to the file.The file can be removed by using the ALTER DATABASE statement.
