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

by

Troubleshooting Guide: Error 4981 – ALTER TABLE SWITCH Failure Due to Read-Only Filegroup

Why This Error Happens

This error occurs because the table you are attempting to switch contains LOB (Large Object Binary) data—such as VARCHAR(MAX), TEXT, or XML columns—that physically resides on a filegroup currently marked as READ-ONLY. SQL Server’s ALTER TABLE SWITCH operation requires metadata-only updates across all data pages, including LOB pages; if the underlying filegroup cannot be written to, the engine cannot validate or update the partition metadata, causing the switch to fail. This typically happens after a filegroup has been set to read-only for archival purposes, or during a restore operation where file permissions were altered.

Step-by-Step Resolution Guide

Step 1: Identify the Problematic Filegroup

  • Run the following query to locate which filegroup holds the LOB data for the specific table mentioned in the error message:
SELECT fg.name AS FilegroupName, fg.is_read_only 
FROM sys.filegroups fg
JOIN sys.tables t ON t.lob_data_space_id = fg.data_space_id
WHERE t.name = 'YourTableName';
  • Confirm the is_read_only column returns 1 (True) for the identified filegroup.

Step 2: Temporarily Disable Read-Only Mode

  • Ensure no active connections are using the database in Single-User mode if required, though generally this can be done in Multi-User mode for Read-Only toggles.
  • Execute the ALTER DATABASE command to switch the specific filegroup back to Read-Write:
ALTER DATABASE YourDatabaseName 
MODIFY FILEGROUP YourFilegroupName READWRITE;
  • Verify in Windows Server 2025 File Explorer that the underlying .ndf or .mdf file properties are not set to “Read-only” at the OS level, as this can sometimes override SQL settings.

Step 3: Execute the Partition Switch

  • Re-run the original ALTER TABLE ... SWITCH PARTITION command now that the filegroup accepts write operations.
  • Ensure the schema of the source and target tables match exactly (including compression settings) to avoid a secondary error immediately following this fix.

Step 4: Revert Filegroup to Read-Only (Optional)

  • If the filegroup was intentionally read-only for archival/performance reasons, set it back immediately after the switch completes:
ALTER DATABASE YourDatabaseName 
MODIFY FILEGROUP YourFilegroupName READONLY;
  • Document this change in your maintenance plan to prevent the same error during the next scheduled partition maintenance window.

Alternative or Verification Steps

  • Verification: Query sys.partitions and sys.allocation_units to confirm the data has physically moved to the new partition without errors, and check the SQL Server Error Log for the absence of Error 4981 on the next attempt.
  • Alternative: If the filegroup must remain permanently read-only (e.g., compliance requirements), the alternative is to physically move the LOB data to a writable filegroup using a CREATE TABLE operation with a new TEXTIMAGE_ON clause pointing to a read-write filegroup, then repopulate the data before switching.