“Database is in Read-Only Mode” – Causes, Troubleshooting, and Solutions

The error “Database is in read-only mode” occurs when a SharePoint database is set to read-only, preventing users from making changes, adding new data, or updating records. This issue typically arises due to SQL Server settings, storage limitations, database corruption, or SharePoint configuration changes.

In this detailed guide, we will cover:
✅ What causes the “Database is in Read-Only Mode” error?
✅ Step-by-step troubleshooting methods
✅ How to fix and prevent this issue in SharePoint

---

🚨 Common Causes of “Database is in Read-Only Mode” Error

1️⃣ SQL Server Database is Set to Read-Only Mode

• The database might have been manually set to read-only by an administrator.
• This can happen due to maintenance, backups, or accidental configuration changes.

2️⃣ Insufficient Disk Space on SQL Server

• If the SQL Server runs out of storage, it may automatically switch databases to read-only mode to prevent corruption.

3️⃣ Database Files are Marked as Read-Only at the OS Level

• The physical database files (.mdf and .ldf) might have a read-only attribute in Windows File Explorer.
• This can happen due to security policies, permission issues, or failed migrations.

4️⃣ SQL Server Instance is in Single-User Mode

• If SQL Server is running in single-user mode, only one connection can access the database, and other operations may be blocked.

5️⃣ SharePoint Configuration or Patch Installation Issues

• A recent SharePoint update, migration, or configuration change may have caused the database to switch to read-only mode.

6️⃣ Corrupt or Damaged Database Files

• A corrupt SQL Server database may switch to read-only mode automatically to prevent further issues.

---

🛠 How to Fix “Database is in Read-Only Mode” – Step-by-Step Guide

Follow these steps to troubleshoot and resolve the issue.

---

🔍 Step 1: Check Database Read-Only Status in SQL Server

To confirm if the database is in read-only mode, follow these steps:

✅ How to Check Read-Only Status via SQL Query:

1. Open SQL Server Management Studio (SSMS).
2. Connect to your SQL Server instance.
3. Run the following query: SELECT name, state_desc FROM sys.databases WHERE name = 'YourDatabaseName';
4. If the state_desc column shows READ_ONLY, then the database is in read-only mode.

✅ If confirmed, proceed to the next steps to change it back to read-write mode.

---

🔍 Step 2: Set the Database to Read-Write Mode

If the database is in read-only mode, change it to read-write using this SQL command:

✅ How to Change Database to Read-Write Mode:

1. Open SQL Server Management Studio (SSMS).
2. Run the following SQL query: ALTER DATABASE YourDatabaseName SET READ_WRITE;
3. Verify the change by running: SELECT name, state_desc FROM sys.databases WHERE name = 'YourDatabaseName';
4. Restart SQL Server and try accessing SharePoint again.

✅ This should allow users to write data to the database.

---

🔍 Step 3: Check File System Permissions for Database Files

If the physical database files are set to read-only, SharePoint won’t be able to write data.

✅ How to Check and Remove Read-Only Attribute in Windows:

1. Open File Explorer on the SQL Server machine.
2. Navigate to the folder containing the database files (.mdf and .ldf).
3. Right-click the files and select Properties.
4. In the General tab, check if Read-Only is enabled.
5. If checked, uncheck the Read-Only box and click Apply → OK.
6. Restart SQL Server and try accessing SharePoint again.

✅ This allows SQL Server to modify the database files properly.

---

🔍 Step 4: Check and Free Up Disk Space on SQL Server

If the SQL Server runs out of disk space, databases may switch to read-only mode automatically.

✅ How to Free Up Space on SQL Server:

1. Open SQL Server Management Studio (SSMS).
2. Run the following query to check available disk space: EXEC xp_fixeddrives;
3. If the available space is low:
   • Delete old backups
   • Move log files to another drive
   • Increase disk space if possible
4. Restart SQL Server and check if SharePoint works.

✅ If disk space was the issue, this should resolve the problem.

---

🔍 Step 5: Verify SQL Server is Not in Single-User Mode

If SQL Server is running in single-user mode, multiple connections can’t be established.

✅ How to Check SQL Server Mode:

1. Run this command in SSMS: SELECT SERVERPROPERTY('IsSingleUser');
2. If it returns 1, the server is in single-user mode.
3. To change it back to multi-user mode, run: ALTER DATABASE YourDatabaseName SET MULTI_USER;

✅ Now multiple users should be able to access the database.

---

🔍 Step 6: Check SharePoint Configuration and Patch Updates

Recent patches or updates may have changed database settings.

✅ How to Fix Configuration Issues:

1. Run SharePoint Configuration Wizard
   • Open SharePoint Central Administration.
   • Click Configuration Wizards → Run Configuration Wizard.
2. Verify Database Status in SharePoint:
   • Open SharePoint Management Shell and run: Get-SPDatabase | Select-Object Name, ReadOnly
   • If ReadOnly = True, update it with: $db = Get-SPDatabase -Identity "YourDatabaseID" $db.SetReadOnly($false) $db.Update()

✅ Now SharePoint should be able to write to the database.

---

🔍 Step 7: Check for Database Corruption and Restore from Backup

If the database is corrupt, you may need to restore a backup.

✅ How to Check for Database Corruption:

1. Run the following command in SSMS: DBCC CHECKDB('YourDatabaseName') WITH NO_INFOMSGS, ALL_ERRORMSGS;
2. If errors are found, try repairing the database: ALTER DATABASE YourDatabaseName SET SINGLE_USER; DBCC CHECKDB('YourDatabaseName', REPAIR_ALLOW_DATA_LOSS); ALTER DATABASE YourDatabaseName SET MULTI_USER;

✅ If the database is too corrupt, restore from the latest backup.

---