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

by

Troubleshooting Guide: Error 4980 — ALTER TABLE SWITCH Failed Due to Read-Only Filegroup (LOB Data)

Why This Error Happens

This error occurs because the ALTER TABLE...SWITCH operation requires full read-write access to every filegroup associated with the partition being switched, including filegroups that store LOB (Large Object) data types such as VARCHAR(MAX), NVARCHAR(MAX), VARBINARY(MAX), or XML. SQL Server 2025 needs to update internal metadata pointers and verify data consistency during the switch, which is impossible if the target filegroup is marked read-only. This typically happens in sliding window partition scenarios where older data has been archived to a read-only filegroup for storage optimization, but a switch operation (in or out) is still being attempted against that partition.

Step-by-Step Resolution Guide

Step 1: Identify the Affected Filegroup and Partition

  • Query the error message to extract the partition number and filegroup name reported in Error 4980.
  • Run the following diagnostic query to map the table to its filegroups:
SELECT 
    ps.partition_number, 
    fg.name AS FilegroupName, 
    fg.is_read_only
FROM sys.partitions p
JOIN sys.allocation_units au ON p.partition_id = au.container_id
JOIN sys.filegroups fg ON au.data_space_id = fg.data_space_id
JOIN sys.partition_schemes ps ON ps.data_space_id = fg.data_space_id
WHERE p.object_id = OBJECT_ID('YourTableName');
  • Confirm whether the flagged filegroup is intentionally read-only (archival) or was mistakenly left in that state.

Step 2: Temporarily Set the Filegroup to Read-Write

  • If the filegroup is not meant to be permanently locked, switch it back to read-write mode to allow the metadata update:
ALTER DATABASE YourDatabaseName 
MODIFY FILEGROUP YourFilegroupName READWRITE;
  • Ensure no other active transactions are locking the filegroup before running this command, as it requires a brief schema modification lock.

Step 3: Execute the ALTER TABLE SWITCH Operation

  • Immediately re-run the original partition switch command while the filegroup is writable:
ALTER TABLE YourTableName SWITCH PARTITION 5 TO YourStagingTable PARTITION 5;
  • Verify the command completes without triggering Error 4980 or related locking timeouts.

Step 4: Revert the Filegroup to Read-Only (If Required)

  • If the filegroup serves as a long-term archive, restore the read-only flag immediately after the switch to maintain your storage tiering strategy and prevent accidental writes:
ALTER DATABASE YourDatabaseName 
MODIFY FILEGROUP YourFilegroupName READONLY;
  • Document this toggle in your maintenance runbooks, as future partition maintenance jobs will require this same two-step toggle.

Alternative or Verification Steps

  • Verify the Fix: Check the filegroup status using SELECT name, is_read_only FROM sys.filegroups; to confirm it returned to the READONLY state successfully (0 or 1) after the maintenance window closed.
  • Alternative Approach: If toggling the filegroup is not feasible due to a maintenance window restriction, consider redesigning the partition function so that LOB data for “hot” partitions resides exclusively on a PRIMARY or dedicated read-write filegroup, isolating it from the archival read-only storage entirely.