13

I have a database backup for which SQL Server Management Studio says that it has three files in it: an .mdf file, an .ndf file and one .ldf file. This secondary data file (the .ndf one) was created for no obvious reason, so I want to remove it altogether (without losing data, of course), preferably during while the database is being restored from the backup.

Is this at all doable?

Anton Gogolev
  • 113,561
  • 39
  • 200
  • 288
  • The purpose of an NDF is to distribute file IO. If you live server has NDF's don't tinker with the schema. Why does the NDF bother you ? – Hassan Syed Feb 02 '10 at 12:03
  • 3
    @Hassan It was created just to overcome NTFS volume fragmentation and it does not have other reasons for its existence. While I agree that NDFs are good, carelessly created NDFs are evil. – Anton Gogolev Feb 02 '10 at 12:07

2 Answers2

14

Ok, found a solution.

First back up the database.

Execute this:

USE database_name;

Then execute this, and replace logical_ndf_file_name with the logical name of your NDF file (which you can easily find out via Database->Properties_Files):

DBCC SHRINKFILE('logical_ndf_file_name', EMPTYFILE);
ALTER DATABASE database_name REMOVE FILE logical_ndf_file_name;
Johannes Rudolph
  • 35,298
  • 14
  • 114
  • 172
Anton Gogolev
  • 113,561
  • 39
  • 200
  • 288
  • was there data in your file ? and if so how long did it take , and was the DB live ? – Hassan Syed Feb 02 '10 at 13:28
  • @Hassan NDF was about 8 Gb, while MDF was around 60 Gb. It took about 20 minutes all in all. Not sure about what you mean by "live". I didn't take it offline, but no queries were run against it either. – Anton Gogolev Feb 02 '10 at 15:07
  • interesting information. By Live I meant production use or customer facing. – Hassan Syed Feb 02 '10 at 15:46
  • @Hassan I doubt that it was available for production use. – Anton Gogolev Feb 02 '10 at 15:59
  • 2
    To add some clarification here, 'ndf_file_name' in the line of code above refers to the logical file name, not the physical. This was not immediately clear to me. – Ben Barreth Mar 25 '13 at 19:40
0

I ran the empty followed by ndf drop during produciton load successfully. I think it is important to run the drop ndf in the same transaction as the empty to ensure the database doesn't try to write to the file you are deleting, but then after an empty the database marks the files unusable, evidenced by attempting another empty shorty after.

user3164106
  • 17
  • 2