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

by

Troubleshooting Guide: Error 5047 – Cannot Change READONLY Property of PRIMARY Filegroup

Why This Error Happens

This error occurs because SQL Server 2025 does not permit the PRIMARY filegroup to be marked as READONLY while it still contains critical system objects or while the database is involved in certain operations that require write access to PRIMARY. It can also happen if the database is part of an Availability Group or is actively being accessed by other sessions, preventing the exclusive lock needed to change the filegroup state. Additionally, insufficient permissions (lack of ALTER rights on the database) can trigger this same error code.

Step-by-Step Resolution Guide

Step 1: Verify Database and Session State

  • Check for active connections using SELECT * FROM sys.dm_exec_sessions WHERE database_id = DB_ID('YourDatabaseName');
  • Ensure no other transactions are holding locks on PRIMARY by querying sys.dm_tran_locks.
  • Switch the database to single-user mode temporarily using ALTER DATABASE YourDatabaseName SET SINGLE_USER WITH ROLLBACK IMMEDIATE;

Step 2: Confirm Filegroup Contents and Permissions

  • Run sys.database_files to confirm whether system tables or indexes are still bound to PRIMARY.
  • Verify your login has db_owner or sufficient ALTER ANY DATABASE permissions using SELECT * FROM fn_my_permissions(NULL, 'DATABASE');
  • If permissions are missing, grant them via SQL Server Management Studio (SSMS) under Security > Logins > Properties > User Mapping.

Step 3: Apply the READONLY Change Correctly

  • Execute the correct syntax: ALTER DATABASE YourDatabaseName MODIFY FILEGROUP [PRIMARY] READONLY;
  • If the database is part of an Availability Group, remove it from the AG temporarily via SSMS or ALTER AVAILABILITY GROUP before retrying.
  • Restart the SQL Server service via Services.msc on Windows Server 2025 if the lock persists after closing all sessions.

Step 4: Check for Replication or Mirroring Conflicts

  • Query sys.database_mirroring and sys.repl_ publications to confirm the database isn’t engaged in replication, since these configurations block filegroup READONLY changes.
  • Temporarily disable replication using sp_removedbreplication 'YourDatabaseName'; if confirmed as the blocker.

Alternative or Verification Steps

  • After applying changes, verify success by running SELECT name, is_read_only FROM sys.filegroups WHERE name = 'PRIMARY'; — the value should return 1.
  • If the error persists, restore the database to a new instance on Windows 11 or a test server, apply the READONLY change there, and then reattach it to validate the filegroup configuration before reapplying to production.