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.

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.

  • 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 Jul 21 '16 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 Jul 22 '16 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 Jul 22 '16 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 Jul 23 '16 at 1:09

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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