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

by

Troubleshooting Guide: Error 4707 – Cannot Truncate Object on READONLY Filegroup

Why This Error Happens

This error occurs because the table or one of its indexes physically resides on a filegroup that has been marked READONLY, which prevents SQL Server from performing the minimally logged deallocation operations required for a TRUNCATE TABLE command. This commonly happens after restoring a database from an archival backup, attaching a read-only data warehouse database, or when a filegroup was intentionally locked to prevent modifications to historical partitioned data. Since truncation requires modifying the page allocation metadata (GAM/SGAM pages), the operation is blocked at the engine level regardless of user permissions.

Step-by-Step Resolution Guide

Step 1: Identify the Affected Filegroup

  • Open SQL Server Management Studio (SSMS) and connect to the SQL Server 2025 instance.
  • Run the following query to determine which filegroup holds the object and its read-only status:
SELECT fg.name AS FilegroupName, fg.is_read_only 
FROM sys.filegroups fg
JOIN sys.indexes i ON fg.data_space_id = i.data_space_id
WHERE i.object_id = OBJECT_ID('YourTableName');
  • Note the exact FilegroupName returned, as you will need it for the next step.

Step 2: Set the Filegroup to READ_WRITE

  • Switch the database context to allow modifications. Ensure no other active sessions are blocking the ALTER command.
  • Execute the following T-SQL command, replacing the placeholders with your actual Database and Filegroup names:
ALTER DATABASE [YourDatabaseName] 
MODIFY FILEGROUP [YourFilegroupName] READ_WRITE;
  • If the command hangs, check for active transactions holding a schema lock on the database via sys.dm_tran_locks.

Step 3: Execute the Truncate Command

  • Once the filegroup status is updated, re-run the original command that triggered the error.
  • Verify the command completes without the 4707 error:
TRUNCATE TABLE [YourTableName];

Step 4: Revert Filegroup to READONLY (Optional)

  • If the filegroup was intentionally set to read-only for compliance or performance reasons (e.g., archival data), revert the setting immediately after the truncate operation to maintain data integrity.
  • Run the following command to restore the original state:
ALTER DATABASE [YourDatabaseName] 
MODIFY FILEGROUP [YourFilegroupName] READONLY;

Alternative or Verification Steps

  • Verification: Query sys.filegroups again to confirm is_read_only has returned to 1 (if reverted) and verify the table row count is 0 using SELECT COUNT(*) FROM YourTableName.
  • Alternative: If the filegroup must remain permanently READONLY due to regulatory constraints, use a DELETE statement instead of TRUNCATE, as DELETE operations are logged differently and may be permitted depending on the specific lock configuration, though this will be significantly slower for large tables.