Troubleshooting Guide: Error 184 – DEFAULT Filegroup Conflict in SQL Server 2025

Why This Error Happens

This error occurs because SQL Server only allows one filegroup per content type (ROWS or FILESTREAM) to be marked as DEFAULT within a database. When a T-SQL script, migration tool, or manual ALTER DATABASE statement attempts to set the DEFAULT property on a second filegroup of the same type, the engine rejects the operation to prevent ambiguous data placement. This is commonly triggered by restored databases, replicated schema scripts, or automated deployment pipelines that don’t check existing filegroup states before applying changes.

Step-by-Step Resolution Guide

Step 1: Identify the Conflicting Filegroups

• Open SQL Server Management Studio (SSMS) and connect to the affected instance.
• Run the following query to list all filegroups and their current DEFAULT status:

  SELECT name, type_desc, is_default 
  FROM sys.filegroups;

• Note which filegroup(s) currently have is_default = 1 for the same type_desc (ROWS or FILESTREAM).

Step 2: Remove the DEFAULT Flag from the Existing Filegroup

• Before assigning a new default, revoke the DEFAULT status from the currently active one.
• Run:

  ALTER DATABASE [YourDatabaseName] 
  MODIFY FILEGROUP [ExistingDefaultFG] DEFAULT;

(Note: Running this toggles it off if it’s already default; verify with the Step 1 query afterward.)

• If the toggle doesn’t clear it, explicitly set a different filegroup as PRIMARY default first to force a reset.

Step 3: Apply the New DEFAULT Filegroup Correctly

• Once the old default is cleared, assign the DEFAULT property to the intended filegroup only.
• Run:

  ALTER DATABASE [YourDatabaseName] 
  MODIFY FILEGROUP [NewTargetFG] DEFAULT;

• Ensure this command is not duplicated later in the same deployment script or DACPAC/BACPAC file, as re-running it on an already-default filegroup causes Error 184.

Step 4: Audit Deployment Scripts and Automation Pipelines

• Check SSDT projects, Flyway/Liquibase migration scripts, or CI/CD pipelines (Azure DevOps, GitHub Actions) for repeated MODIFY FILEGROUP ... DEFAULT statements.
• Add conditional logic (IF NOT EXISTS checks against sys.filegroups) to prevent re-application of DEFAULT on subsequent pipeline runs.

Alternative or Verification Steps

• Re-run the sys.filegroups query from Step 1 — only one filegroup per type_desc should show is_default = 1. If correct, the error is resolved.
• As an alternative, if the error persists during a database restore, use RESTORE DATABASE ... WITH FILE_MOVE options combined with a post-restore script that explicitly resets filegroup defaults before running any schema synchronization tools.