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?