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

by

Troubleshooting Guide: Error 5031 – Cannot Remove File from DEFAULT Filegroup

Why This Error Happens

This error occurs because SQL Server 2025 enforces a strict architectural rule: the PRIMARY (DEFAULT) filegroup must always contain at least one data file to host system tables and catalog views. When an administrator attempts to drop the last remaining .mdf or .ndf file associated with the default filegroup, the Database Engine rejects the operation to prevent corruption of system metadata. This typically happens during database shrink operations or manual file cleanup where the administrator miscounted the remaining files in that specific filegroup.

Step-by-Step Resolution Guide

Step 1: Verify the Filegroup File Structure

  • Open SQL Server Management Studio (SSMS) and connect to the instance.
  • Run the following query to check how many files exist in the target filegroup:
  SELECT name, physical_name, type_desc, state_desc 
  FROM sys.master_files 
  WHERE database_id = DB_ID('YourDatabaseName');
  • Confirm if the file you are trying to drop is indeed the sole resident of the PRIMARY filegroup.

Step 2: Add a New File to the Filegroup (If Deletion is Mandatory)

  • If you must move data off the specific physical file (e.g., due to disk space issues on a specific drive), add a new file to the same filegroup first.
  • Use this syntax to add a placeholder/replacement file:
  ALTER DATABASE YourDatabaseName 
  ADD FILE (NAME = NewPrimaryFile, FILENAME = 'C:\SQLData\NewPrimaryFile.ndf') 
  TO FILEGROUP [PRIMARY];
  • Ensure the path C:\SQLData\ exists and the SQL Server Service Account has write permissions on Windows Server 2025.

Step 3: Empty the Original File

  • Before you can drop the original file, you must empty it so SQL Server migrates the data pages to the newly added file.
  • Execute the EMPTYFILE command to redistribute data:
  DBCC SHRINKFILE (OldFileLogicalName, EMPTYFILE);
  • Wait for the command to complete, as this transfers all extents to other available files in the filegroup.

Step 4: Remove the Original File

  • Once the file is confirmed empty (0 KB of actual data), you can now safely remove it since the filegroup will still contain the new file you added in Step 2.
  • Run the removal command:
  ALTER DATABASE YourDatabaseName REMOVE FILE OldFileLogicalName;

Alternative or Verification Steps

  • Verification: Re-run the sys.master_files query from Step 1 to ensure the old file is gone and the new file is listed with state_desc = ONLINE.
  • Alternative: If the goal was only to relocate the database (not delete data), consider using the OFFLINE, move-file-on-disk, and MODIFY FILE (to update the path) method instead of dropping the file entirely, which avoids the EMPTYFILE migration step.