0

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.

Improve this question

2 Answers 2

Reset to default
3

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.

Improve this answer
4
  • That's exactly what I'm doing, except I'd like to remove file #1 and keep file #2. Both are in the PRIMARY filegroup; are you saying that the first one cannot be removed, by virtue of being the first data file? There's nothing visible in the GUI or sys.database_files to indicate that #1 is different from #2, aside of course from the order they appear.
    – Jon of All Trades
    Commented Jul 21, 2016 at 22:57
  • @JonofAllTrades,the special pages in the primary data file are fixed and cannot be moved to other files. I added an example and link to my answer.
    – Dan Guzman
    Commented Jul 22, 2016 at 0:34
  • Is the primary data file defined as the one at file_id = 1 in sys.database_files? I'm confused because there doesn't seem to be any sign that this particular file is different from any other. I would expect it to be flagged somehow.
    – Jon of All Trades
    Commented Jul 22, 2016 at 18:35
  • 1
    File number 1 is always the primary in my experience but the way to know for sure is with SELECT FILEPROPERTY('LogicalFileName', 'IsPrimaryFile').
    – Dan Guzman
    Commented Jul 23, 2016 at 1:09
-1

You may be able to relocate the primary DB file by backing up the database and restoring it with the option to "Rolocate all files to folder" selected. Then files may be renamed by entering a new name under the "Restore As" column

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.