Troubleshooting Guide: SQL Server Error 5050 — Cannot Change Properties of Empty Filegroup

Why This Error Happens

This error occurs because SQL Server requires every filegroup to contain at least one data file before its properties (such as setting it to READ_ONLY, DEFAULT, or adjusting autogrow settings) can be modified. The filegroup was likely created as a logical container via ALTER DATABASE ... ADD FILEGROUP, but no corresponding data file (.ndf or .mdf) was ever added to it using ADD FILE. SQL Server treats an empty filegroup as an incomplete object and blocks administrative operations on it until it is populated.

Step-by-Step Resolution Guide

Step 1: Identify the Empty Filegroup

• Connect to the instance using SQL Server Management Studio (SSMS) 21 or sqlcmd on Windows Server 2025 / Windows 11.
• Run the following query to list filegroups and check file counts:

  SELECT fg.name AS FilegroupName, COUNT(f.file_id) AS FileCount
  FROM sys.filegroups fg
  LEFT JOIN sys.database_files f ON fg.data_space_id = f.data_space_id
  GROUP BY fg.name;

• Confirm the filegroup in the error message shows a FileCount of 0.

Step 2: Add a Data File to the Filegroup

• Use ALTER DATABASE to add at least one file to the empty filegroup:

  ALTER DATABASE [YourDatabaseName]
  ADD FILE
  (
      NAME = N'YourFileLogicalName',
      FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\DATA\YourFile.ndf',
      SIZE = 64MB,
      FILEGROWTH = 64MB
  )
  TO FILEGROUP [YourFilegroupName];

• Verify the target path exists and that the SQL Server service account has write permissions on that directory.

Step 3: Retry the Property Change

• Re-run the original command that triggered Error 5050 (e.g., setting the filegroup to READ_ONLY or DEFAULT):

  ALTER DATABASE [YourDatabaseName]
  MODIFY FILEGROUP [YourFilegroupName] READONLY;

• Check the SQL Server Error Log (via SSMS Object Explorer > Management > SQL Server Logs) to confirm no further errors were raised.

Step 4: Clean Up Unused Empty Filegroups (Optional)

• If the filegroup was created by mistake and is not needed, remove it instead of adding a file:

  ALTER DATABASE [YourDatabaseName]
  REMOVE FILEGROUP [YourFilegroupName];

• This avoids leaving orphaned, unusable filegroups in the database schema.

Alternative or Verification Steps

• Verify the fix by re-running the filegroup/file count query from Step 1 — the filegroup should now show FileCount >= 1 and the property change command should execute without Error 5050.
• If the issue persists, check for an open transaction or active session blocking schema changes using sp_who2 or sys.dm_tran_locks, then retry after resolving the blocking session.