Unable to write to ndf after shrink emptyfile

Question

I am doing some file balancing practice/experiments on AdventureWorks and pages stopped writing to the ndf file (AdventureWorks2014_Data2.ndf).

Several tests I was running should have written pages to *Data2.ndf but did not.

Getting very frustrated I tried to shrink the mdf to ndf and if failed.

Msg 2556, Level 16, State 1, Line 3 There is insufficient space in the filegroup to complete the emptyfile operation.

Both mdf and ndf have 300MB allocated, this does not make sense. mdf has 187mB used, ndf has 0MB used,

There have been multiple t-log backups taken in the time span.

I created *Data3.ndf, and shrunk from mdf, everything (excpet meta data) moved to Data3.ndf, nothing goes to *Data2.ndf.

Msg 2555, Level 16, State 1, Line 3 Cannot move all contents of file "AdventureWorks2014_Data" to other places to complete the emptyfile operation.

What is wrong with *Data2.mdf?

Although you don't explicitly say in your question, it sounds like you ran a shrinkfile (empty) on data2.ndf. If that's the case, you basically told Sql Server to never write data to that file again and move all existing data from that file to other files in the same filegroup. You should be able to delete data2.ndf (if it is truly empty) and then add the file back. Then you can run a bunch of rebuild index commands which should start populating the newly added file again. — Scott Hodgin, Mar 27 '17 at 15:05
Correct, as the mdf never gets completely empty you can write to it after shrinkfile (empty) without issue. It was a complete surprise that you would not be able to write to the ndf again. I thought the file was corrupted but was unable to identify how. Now it seems like a design feature (bug). I should add, I did several things to both the ndf and mdf, identifying that it was the shrinkfile (empty) that was the cause, was the challenge. — James Jenkins, Mar 27 '17 at 15:15

James Jenkins · Accepted Answer · 2017-03-27 14:51:07Z

What I learned,

I Shrunk emptyfile back to .mdf no problem, but when I tried to shrink emptyfile back to Data3.ndf same error.

It seems once you have shrunk emptyfile from an .ndf you have to delete (Remove) it, you can't write to again.

Additionally if you try to recreate the ndf with the same name, you will not be able to, until you run a t-log backup.

Optimal, don't expect to reuse an ndf once you have shrunk emptyfile. If you need to write back to an ndf use a different name.

If you do need (or want) to write to the same ndf name:

1 Answer 1