insufficient space in the filegroup

Vijay Kumar 2,011 Reputation points
2020-10-14T00:33:27.153+00:00

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.

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,314 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,523 questions
{count} votes

Accepted answer
  1. MelissaMa-MSFT 24,176 Reputation points
    2020-10-14T01:15:15.293+00:00

    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.


4 additional answers

Sort by: Most helpful
  1. MelissaMa-MSFT 24,176 Reputation points
    2020-10-14T00:54:54.283+00:00

    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.


  2. Shashank Singh 6,246 Reputation points
    2020-10-14T04:51:40.807+00:00

    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

    0 comments No comments

  3. MelissaMa-MSFT 24,176 Reputation points
    2020-10-14T05:28:06.56+00:00

    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.

    0 comments No comments

  4. PB-2598 1 Reputation point
    2020-10-14T17:56:10.577+00:00

    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;

    0 comments No comments