I am doing some file balancing practice/experiments on AdventureWorks and pages stopped writing to the ndf file (AdventureWorks2014_Data2.ndf).
Early tests were working as expected. Proportional fill, spreads new pages across multiple files in file group.
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?
Best Answer
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: