insufficient space in the filegroup

Question

Hi

Original file size is 250 GB.

Created additional 5 files under same file group in order to balance load.

While i execute DBCC SHRINKFILE (N'Originalfile' , EMPTYFILE)

Msg 2556, Level 16, State 1, Line 3
There is insufficient space in the filegroup to complete the emptyfile operation.

we having sufficient space in disk.

Answer 1

Hi @Vijay Kumar ,

High level process for splitting a filegroup into multiple files

1. Add several new empty data files to the file group
2. Cap the new files so they cannot auto grow
3. Empty the original data file
4. Readjust all files so they each have the same amount of free space / re-enable autogrowth
5. Shrink the original datafile to the same size of the new files

Please refer below link for more details and check whether it is helpful to you. Thanks.
Split a file group into multiple data files

Best regards
Melissa

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

Hi @Vijay Kumar ,

If you would like to empty a file group as a whole, you must delete the objects (tables or indexes) allocated on this file group, or move them to other file groups. DBCC SHRINKFILE will not help you do this work.

After clearing the data and objects that should be cleared in the data file, and confirming that the data file (group) has enough free space, the administrator can issue the DBCC SHRINKFILE command to shrink or empty the specified file.

If your issue was caused by the Full Text search indexes, you could refer more details from the marked answer mentioned in below link:
There is insufficient space in the filegroup to complete the emptyfile operation

Best regards
Melissa

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 3

Created additional 5 files under same file group in order to balance load.

Unless these files are created on different physical disks creating them on same disk with different files will not let you gain anything. Ofcourse will not let you "balance load" in terms of I/O. It also depends on storage and RAID configuration

This article benchmarking-do-multiple-data-files-make-a-difference is good read for you to understand would multiple files really help.

There is insufficient space in the filegroup to complete the emptyfile operation.

Do you have sufficient space on destination file ? It seems like space issue to me

Answer 4

Hi @Vijay Kumar ,

When emptying a file in a filegroup, DBCC will attempt to move the pages to other files in the same group, if there's not enough space in the other files you'll get this issue.

Besides, have you tried with dropping the indexes as I mentioned in my first answer?

What is the recovery model for the database associated with this file? If it is full, you could try to back up transaction logs firstly.

In addition, you could use follow below steps to proceed.

Step 1: Run below query:

SELECT OBJECT_NAME(object_id), *  
FROM sys.data_spaces ds  
INNER JOIN sys.allocation_units au ON  
 ds.data_space_id = au.data_space_id  
INNER JOIN sys.partitions p ON  
 au.container_id =   
 CASE   
 WHEN au.type = 2 THEN p.partition_id  
 ELSE p.hobt_id   
 END  
WHERE ds.name = 'filegroupname' --Your file group name  

Step 2:Either you have to move that objects to another file or drop that one (not feasible on production).

Step 3: RUN below command to empty your original file.

DBCC SHRINKFILE (N'Originalfile' , EMPTYFILE)  

Step 4: After this try to remove file from database property.

Please also refer below link for more details which may be helpful to you.
Unable to Shrink File

Best regards
Melissa

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 5

VijayKumar, is this originalfile in the Primary filegroup? I have noticed this before. If the file is in secondary or other filegroup, the emptyfile will work well as documented. But if the file is in Primary filegroup, the emptyfile gives errors. I have two files with more space added and tried to empty the first file. It will move most of the data but not all. Check how much space is left on each file with this query.
SELECT name ,size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0 AS AvailableSpaceInMB
FROM sys.database_files;