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

by

Troubleshooting Guide: Error 4978 — ALTER TABLE SWITCH Failed on Read-Only Filegroup

Why This Error Happens

This error occurs when SQL Server 2025 attempts to perform a partition switch operation (ALTER TABLE...SWITCH) on a partition that physically resides in a filegroup marked as read-only. Because the SWITCH operation requires metadata updates to the partition structure, SQL Server cannot write these changes if the underlying filegroup does not permit I/O modifications. This commonly happens with sliding window scenarios where older partitions have been intentionally archived to read-only filegroups for performance or compliance reasons, but a SWITCH operation was mistakenly targeted at that partition.

Step-by-Step Resolution Guide

Step 1: Identify the Affected Filegroup and Partition

  • Run the following query to identify which filegroup is tied to the problematic partition:
  • SELECT fg.name AS FilegroupName, fg.is_read_only, p.partition_number FROM sys.filegroups fg JOIN sys.destination_data_spaces dds ON fg.data_space_id = dds.data_space_id JOIN sys.partition_schemes ps ON dds.partition_scheme_id = ps.data_space_id JOIN sys.partitions p ON p.partition_number = dds.destination_id WHERE fg.is_read_only = 1;
  • Confirm the partition number referenced in the Error 4978 message matches the output.

Step 2: Temporarily Set the Filegroup to Read-Write

  • Open SQL Server Management Studio (SSMS) or a sqlcmd session connected to the instance.
  • Execute: ALTER DATABASE [YourDatabaseName] MODIFY FILEGROUP [YourFilegroupName] READWRITE;
  • Ensure no other active transactions or reporting jobs are using the filegroup in read-only mode during this window, as this will temporarily allow writes.

Step 3: Perform the ALTER TABLE SWITCH Operation

  • Re-run the original partition switch command once the filegroup is writable:
  • ALTER TABLE [dbo].[YourTable] SWITCH PARTITION 5 TO [dbo].[YourStagingTable] PARTITION 5;
  • Monitor the SQL Server Error Log (located at C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\ERRORLOG on Windows Server 2025) for confirmation of success.

Step 4: Revert the Filegroup to Read-Only

  • Immediately after the switch completes, restore the original read-only state to maintain your archival policy:
  • ALTER DATABASE [YourDatabaseName] MODIFY FILEGROUP [YourFilegroupName] READONLY;
  • Verify no pending transactions are open against that filegroup before reverting, using sys.dm_tran_active_transactions.

Step 5: Validate Partition Function and Scheme Alignment

  • If the error persists, check whether the partition function boundaries were recently split or merged.
  • Use sys.partition_range_values to confirm the target partition boundary matches the intended read-write filegroup mapping before attempting the switch again.

Alternative or Verification Steps

  • Verify the fix by querying sys.filegroups to confirm is_read_only = 0 during the switch window and is_read_only = 1 after reverting.
  • As an alternative, if frequent switching into this partition is required, consider redesigning the partition scheme so that the “swap” partition range always maps to a dedicated read-write filegroup, separate from your long-term read-only archive filegroups.