When working with SQL Server, error messages can be a valuable source of troubleshooting information. It is important to carefully read and understand the error messages, as they often provide clues to the underlying issue. In this blog post, we will discuss a common error message related to read-only databases in SQL Server and provide a simple solution to resolve it.

Recently, one of our readers encountered the following error message:

Msg 3906, Level 16, State 1, Line 10 Failed to update database "ProdDB" because the database is read-only.

At first glance, this error message may seem self-explanatory. The database is marked as read-only, preventing any write operations. However, it is worth exploring the solution in more detail.

To demonstrate the issue, let’s create a sample database and mark it as read-only:


CREATE DATABASE [ReadOnlyDB]
CONTAINMENT = NONE ON PRIMARY (NAME = N'ReadOnlyDB', FILENAME = N'C:\Temp\ReadOnlyDB.mdf', SIZE = 5120KB, FILEGROWTH = 1024KB)
LOG ON (NAME = N'ReadOnlyDB_log', FILENAME = N'C:\Temp\ReadOnlyDB_log.ldf', SIZE = 2048KB, FILEGROWTH = 10%)
GO

USE ReadOnlyDB
GO

CREATE TABLE tbl_SQLAuth (id INT)
GO

USE MASTER
GO

ALTER DATABASE [ReadOnlyDB] SET READ_ONLY
GO


Now, if we try to insert values into the table, we will encounter the same error message:


USE ReadOnlyDB
GO

INSERT INTO tbl_SQLAuth VALUES (1), (2)
GO


The error message confirms that the database is read-only and cannot be updated. To resolve this issue, we need to remove the read-only attribute from the database. This can be achieved using the following T-SQL:


USE MASTER
GO

ALTER DATABASE [ReadOnlyDB] SET READ_WRITE WITH NO_WAIT
GO


Once the database is set to read-write mode, we can successfully perform write operations:


USE ReadOnlyDB
GO

INSERT INTO tbl_SQLAuth VALUES (1), (2)
GO


Now, the insert statement will execute without any errors.

As a best practice, it is important to clean up after testing. Here is the script to drop the sample database:


USE MASTER
GO

DROP DATABASE ReadOnlyDB
GO


In conclusion, understanding and resolving the SQL Server read-only database error is crucial for maintaining a functional database environment. By carefully reading error messages and following the appropriate steps, such as changing the database to read-write mode, we can overcome this issue. If you have encountered this error before or have used read-only databases in your environment, we would love to hear about your experiences.

Thank you for reading!