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

by

Troubleshooting Guide: Error 3906 – Database Is Read-Only

Why This Error Happens

This error occurs because the target database has its READ_ONLY property explicitly set, either manually by an administrator or automatically following operations like restoring from a read-only source, attaching a database from read-only media, or after certain Always On Availability Group failover scenarios. SQL Server 2025 also enforces this state when the database is part of a Log Shipping secondary role or when disk-level permissions on the underlying .mdf/.ndf files are restricted to read-only at the Windows file system level. Until the database state or file permissions are reverted, any INSERT, UPDATE, DELETE, or schema-altering transaction will be rejected with Error 3906.

Step-by-Step Resolution Guide

Step 1: Verify Current Database State

  • Open SQL Server Management Studio (SSMS) or a query window and run:
  SELECT name, state_desc, is_read_only 
  FROM sys.databases 
  WHERE name = 'YourDatabaseName';
  • Confirm whether is_read_only returns 1, confirming the database-level flag is the cause.

Step 2: Change the Database to Read-Write Mode

  • Ensure no active connections are using the database before altering its state.
  • Run the following T-SQL command with sufficient privileges (sysadmin or db_owner):
  ALTER DATABASE YourDatabaseName SET READ_WRITE WITH ROLLBACK IMMEDIATE;
  • The WITH ROLLBACK IMMEDIATE clause forcibly disconnects other users and rolls back open transactions to apply the change instantly.

Step 3: Check Windows File System Permissions

  • Navigate to the physical location of your database files (commonly C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\DATA\).
  • Right-click the .mdf and .ldf files, go to Properties > Security, and ensure the NT SERVICE\MSSQLSERVER (or your custom SQL service account) has Full Control or at least Modify permissions.
  • Remove any inherited “Read-only” attribute checkbox under the General tab if it is checked.

Step 4: Review Log Shipping or Availability Group Configuration

  • If the database is a secondary replica in an Always On Availability Group, confirm this is expected behavior (secondaries are read-only by design) and redirect write operations to the Primary replica instead.
  • If using Log Shipping, verify this instance is not configured as a Secondary role recipient unintentionally.

Alternative or Verification Steps

  • Re-run the write operation (INSERT/UPDATE) that originally triggered Error 3906 to confirm the database now accepts transactions.
  • If the ALTER DATABASE command hangs due to active sessions, use sp_who2 to identify blocking SPIDs, or restart the SQL Server service via services.msc to clear locks before retrying the state change.