0

Executed the DBCC Shrinkfile('file1',emptyfile) successfully on first file in the filegroup and removed the file that I shrank.

Executed the DBCC ShrinkFile('file2',emptyfile) unsuccessfully on file 2. File 2 is in the same file group that file1 was in. Result 2555 error:

Cannot move all contents of file "file2" to other places to complete the emptyfile operation.

Restarted SQL, executed script from point 2, with same result.

I am ultimately trying to remove the file group that the file is a member of and the file group has only one remaining file in it. The file is has current allocated space of 1 MB and available free space of .81 MB

1

If it's saying the file is not empty, it's not empty. You need to figure out what's there and either rebuild it on another filegroup manually, or drop it. EMPTYFILE only works on the last file if there are no objects at all, and of course this is not possible on the PRIMARY filegroup.

My first guess is there's a table, index, or partition scheme still associated with this filegroup:

DECLARE @fgid int;

SELECT @fgid = data_space_id
  FROM sys.filegroups
  WHERE name = N'smoochies';

SELECT [object] = o.name, [index] = i.name
  FROM sys.objects AS o
  INNER JOIN sys.indexes AS i
  ON o.[object_id] = i.[object_id]
  WHERE i.data_space_id = @fgid
    OR i.data_space_id IN 
    (
      SELECT partition_scheme_id
        FROM sys.destination_data_spaces AS ds
        WHERE data_space_id = @fgid
    );

SELECT ps.name
  FROM sys.destination_data_spaces AS ds
  INNER JOIN sys.partition_schemes AS ps
  ON ds.partition_scheme_id = ps.data_space_id
  WHERE ds.data_space_id = @fgid
  GROUP BY ps.name;

When you find the thing (it could be an index on an empty table, an empty heap, or just a partition scheme still referencing the filegroup you want to remove), you'll have to drop / rebuild / shift objects to a new partition scheme. If the partition currently referenced is the "permanent" partition (first in RANGE RIGHT or last in RANGE LEFT), see this answer.

| improve this answer | |
  • 1
    I appreciate your response. when I execute the queries you provided, they return nothing for the object and index columns. – Paul McKibben Jun 10 at 21:18
  • @PaulMcKibben Can you check if it is a partition scheme still associated with the filegroup? – Aaron Bertrand Jun 11 at 14:27
0

The "emptyfile" option actually migrates all data from the specified file to other files in the same filegroup.

In other words, EMPTYFILE migrates data from a specified file to other files in the same filegroup.

My guess is that your "file2" is the only remaining file in this filegroup and that's probably why you cannot empty it.

Make sure nothing is using it before you delete it

| improve this answer | |
  • While you might be able to skip the EMPTYFILE operation for the last file if the file is already "empty" (though I haven't tested this), you should not get this specific error message just because the file happens to be the last file in the filegroup. You would only get the error message if it is the last file in the filegroup and there is something left to move. – Aaron Bertrand Jun 11 at 14:40

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.