In dealing with fragmented disk space, I added a second data file to one of my databases. Most of the data is now in the the second file, and I'd like to drop the first to simplify things. However, it's the primary data file:
Msg 5020, Level 16, State 1, Line 3
The primary data or log file cannot be removed from a database.
So how do I mark the second file as primary, so I can delete the first file?
In Googling this problem, I see lots of people trying to remove filegroups, but I'm just trying to drop one file from a group.
Same situation, but without the problem regarding "primary data file": https://stackoverflow.com/questions/2183742/remove-merge-sql-servers-database-secondary-data-file.
Similar, but for log files which have different issues: https://stackoverflow.com/questions/10839374/sql-server-database-has-2-log-files-and-i-want-to-remove-one-how. I am interested in removing a data file.
A similar situation, but there the problem is in emptying the file: http://www.sqlservercentral.com/Forums/Topic464326-5-1.aspx.
Best Answer
The primary database file can never be removed because it contains special page structures that exist only on that file and cannot be relocated. If you want a single database file, move all data from the secondary file using DBCC SHRINKFILE with the EMPTYFILE option and then remove the secondary file. Finally, rebuild/reorganize indexes to reduce fragmentation.
An example of unmovable page is the database boot page, which is page 9 of the primary data file of every database. See Paul Randal's Anatomy of a Page for details.