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

by

Troubleshooting Guide: Error 3740 – Cannot Drop Object on Read-Only Filegroup

Why This Error Happens

This error occurs because the table, index, or object you are attempting to drop has pages physically residing on a filegroup that is currently marked as READ-ONLY. SQL Server 2025 requires write access to the filegroup to update system metadata and reclaim space during a DROP TABLE or DROP INDEX operation, so it blocks the action to prevent corruption. This is commonly seen on archival databases, SAN-replicated volumes, or databases restored from read-only backups where the PRIMARY or secondary filegroup property was not reset after migration.

Step-by-Step Resolution Guide

Step 1: Identify the Affected Filegroup

  • Open SQL Server Management Studio (SSMS) or Azure Data Studio and connect to the instance.
  • Run the following query to identify which filegroup is read-only:
  SELECT name, is_read_only FROM sys.filegroups;
  • Note the specific name of the filegroup returned with is_read_only = 1.

Step 2: Verify File System Permissions (Windows Server 2025 / Windows 11)

  • Navigate to the physical data file location (e.g., C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\DATA\).
  • Right-click the .ndf or .mdf file associated with the filegroup and select Properties.
  • Ensure the Read-only attribute checkbox under the General tab is unchecked.
  • Confirm the NT SERVICE\MSSQLSERVER account has Modify permissions under the Security tab.

Step 3: Alter the Filegroup to Read-Write

  • Execute the following T-SQL command to change the database state, replacing YourDatabaseName and YourFilegroupName with your specific values:
  ALTER DATABASE YourDatabaseName 
  MODIFY FILEGROUP YourFilegroupName READWRITE;
  • If the database is part of an Availability Group (AG) in SQL Server 2025, ensure you run this command on the Primary Replica only.

Step 4: Retry the Drop Operation

  • Once the filegroup status is updated, immediately re-run your original DROP TABLE or DROP INDEX command.
  • If the object drop succeeds without the 3740 error, proceed to revert the filegroup to read-only (if required for compliance) using ALTER DATABASE ... MODIFY FILEGROUP ... READONLY;.

Alternative or Verification Steps

  • Verification: Re-run the sys.filegroups query from Step 1. The is_read_only column should now show 0 for the target filegroup, and the DROP command should execute without throwing Error 3740.
  • Alternative: If you cannot change the filegroup to read-write (e.g., due to SAN snapshot locks), you may need to export the required data to a new table located on the PRIMARY filegroup and drop the entire read-only filegroup instead of the single object.