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

by

Troubleshooting Guide: Error 4979 – ALTER TABLE SWITCH Readonly Filegroup Failure

Why This Error Happens

This error occurs because the target or source table involved in the ALTER TABLE SWITCH operation resides in a filegroup that has been marked as READONLY, preventing SQL Server from modifying the metadata pointers required to complete the partition switch. This is common in archival or historical partitions where filegroups are intentionally locked down to prevent data drift, but it blocks maintenance operations that require metadata writes. Additionally, this can happen inadvertently if a database was restored with READ_ONLY options or if a filegroup was set to readonly during an ETL process and never reverted.

Step-by-Step Resolution Guide

Step 1: Identify the Readonly Filegroup

  • Run the following query to identify which filegroup is causing the conflict: SELECT name, is_read_only FROM sys.filegroups;
  • Cross-reference the filegroup name returned in the Error 4979 message with the results to confirm the specific filegroup requiring changes.
  • Check the table’s current partition scheme to verify which filegroup the specific partition resides on using sys.partitions and sys.destination_data_spaces.

Step 2: Temporarily Set the Filegroup to Read/Write

  • Open SQL Server Management Studio (SSMS) or a query window connected to the specific instance.
  • Execute the command to toggle the filegroup status: ALTER DATABASE [YourDatabaseName] MODIFY FILEGROUP [YourFilegroupName] READWRITE;
  • Ensure you have exclusive access or that no other active transactions are blocking this schema modification, as this requires a brief schema stability lock.

Step 3: Execute the Switch Operation

  • Re-run the original partition switch command, for example: ALTER TABLE [Staging_Table] SWITCH PARTITION 5 TO [Main_Table] PARTITION 5;
  • Verify the operation completes instantly, confirming the metadata switch (not data movement) has been processed correctly.

Step 4: Revert the Filegroup to Readonly (Optional)

  • If your data governance policy requires the filegroup to remain locked after the switch, revert the changes immediately.
  • Execute: ALTER DATABASE [YourDatabaseName] MODIFY FILEGROUP [YourFilegroupName] READONLY;
  • Confirm no other scheduled jobs (such as nightly ETL loads) are expected to write to this filegroup before locking it again.

Alternative or Verification Steps

  • Verification: Query sys.partitions post-switch to confirm the partition count and row counts have successfully moved between the staging and production tables.
  • Alternative: If you cannot alter the filegroup due to compliance restrictions, consider staging the switch on a different, writable filegroup first, and then use ALTER TABLE SWITCH once the data lands in a read/write enabled partition before archiving.