How to fix Error 2556 on Windows Server 2025 / Windows 11

by

Why This Error Happens

Error 2556 occurs when SQL Server attempts to run DBCC SHRINKFILE with the EMPTYFILE option, but the remaining files in the filegroup do not have enough combined free space to hold the data being migrated off the target file. This typically happens because other files in the same filegroup are nearly full, have restrictive autogrowth settings, or are capped by a maximum size limit. It can also occur if large objects (LOB data) or unmovable pages exist on the file, preventing data from relocating even if space appears technically available.

Step-by-Step Resolution Guide

Step 1: Check Filegroup and File Space Usage

  • Open SQL Server Management Studio (SSMS) and run sp_helpfile or query sys.database_files to list all files in the affected filegroup.
  • Execute the following query to check free space per file:
  SELECT name, size/128.0 AS SizeMB, 
  (size/128.0 - FILEPROPERTY(name, 'SpaceUsed')/128.0) AS FreeSpaceMB
  FROM sys.database_files;
  • Identify whether other files in the filegroup have enough free space to absorb the data from the file being emptied.

Step 2: Add a New Data File to the Filegroup

  • If existing files lack sufficient space, add a new file to the same filegroup to provide additional capacity.
  • In SSMS, navigate to Database Properties > Files > Add, or run:
  ALTER DATABASE [YourDB] 
  ADD FILE (NAME = 'NewFile1', FILENAME = 'D:\SQLData\NewFile1.ndf', SIZE = 5GB)
  TO FILEGROUP [YourFileGroup];
  • Ensure the drive specified (e.g., D:\SQLData\) has adequate physical disk space on the Windows Server 2025 host.

Step 3: Adjust Autogrowth and Maximum File Size Settings

  • Check if existing files have a capped Maximum File Size limit preventing growth.
  • In SSMS, go to Database Properties > Files, and change Autogrowth/Maxsize to “Unrestricted” or a higher fixed value.
  • Alternatively, run:
  ALTER DATABASE [YourDB] 
  MODIFY FILE (NAME = 'ExistingFile', MAXSIZE = UNLIMITED, FILEGROWTH = 512MB);

Step 4: Retry the EMPTYFILE Operation

  • Once space is confirmed, re-run the shrink command:
  DBCC SHRINKFILE (N'FileToEmpty', EMPTYFILE);
  • Monitor the SQL Server error log and Windows Event Viewer (Application log) for confirmation that the operation completed without further space errors.

Alternative or Verification Steps

  • After the operation succeeds, run DBCC SHRINKFILE (N'FileToEmpty', EMPTYFILE) again to confirm the file now shows 0 KB used space, then safely remove it using ALTER DATABASE [YourDB] REMOVE FILE FileToEmpty.
  • If space remains insufficient even after adding files, consider moving the database to a volume with more free disk space or temporarily disabling other heavy transactional processes to allow data to shrink/migrate during a maintenance window.