1

Trying to remove an extra database file from a database using EMPTYFILE on SQL Server 2008 R2.

use [MYDATABASE] 
        DBCC SHRINKFILE (MY_DATABASE_7, EMPTYFILE); 
        use master 
        ALTER DATABASE [MY_DATABASE] 
        REMOVE FILE MY_DATABASE_7; 

And getting this error

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

The other database files aren't limited in growth in anyway. There is more than enough disk space

And there is practically nothing left in the 7th file.

enter image description here

So there is no reason why I can't remove this 7th file.

Any thoughts as to why SQL Server is rejecting the EMPTYFILE ?

  • Did you try freeing up system cache FREESYSTEMCACHE and proc cache and rerun the operation. There will be a performance hit if you are doing it on a PROD server. – Kin Shah Feb 11 '15 at 17:48
  • 1
    I am sure that this is not the problem, but remember that the file which contains the system tables cannot be fully emptied. – RLF Feb 11 '15 at 18:39
  • @MaxVernon - Its done. – Craig Efrein Apr 25 '18 at 14:44
1

Increasing the size of the other database files by 1GB resolved the problem.

| improve this answer | |

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.