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 ?

Improve this question
3
  • 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
    Commented Feb 11, 2015 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
    Commented Feb 11, 2015 at 18:39
  • @MaxVernon - Its done.
    – Craig Efrein
    Commented Apr 25, 2018 at 14:44

1 Answer 1

Reset to default
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 and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.