Some error messages are a great source of troubleshooting when things go wrong. I always request my blog readers to read what the error messages are conveying. Most of the solutions are very much documented as part of the message. I must agree that over the years the error messages have become more descriptive in SQL Server.
On a similar note, recently one of the DB’s sent me a mail stating they were getting the below error message and they thought I had something on the blogs to help them solve the same.
Msg 3906, Level 16, State 1, Line 10
Failed to update database “ProdDB” because the database is read-only.
On careful search, I haven’t seem to have written about the resolution. When I looked at the error message, I thought it was quite explanatory and didn’t need any mention about solution. Your database is marked as ReadOnly and we are not able to write into them. So why the fuss, just mark your database as read-write.
After that incident, I thought of writing this blog to show how this can be solved with a simple code. Let us get into the script next. Let us create our database for testing.
CREATE DATABASE [ReaOnlyDB]
CONTAINMENT = NONE
ON PRIMARY
( NAME = N'ReaOnlyDB', FILENAME = N'C:\Temp\ReaOnlyDB.mdf' , SIZE = 5120KB , FILEGROWTH = 1024KB )
LOG ON
( NAME = N'ReaOnlyDB_log', FILENAME = N'C:\Temp\ReaOnlyDB_log.ldf' , SIZE = 2048KB , FILEGROWTH = 10%)
GO
USE ReaOnlyDB
GO
CREATE TABLE tbl_SQLAuth (id INT)
GO
We have gone ahead and created the table which we will use for inserting later in the code. Let us mark the just created database as ReadOnly.
USE MASTER
GO
ALTER DATABASE [ReaOnlyDB] SET READ_ONLY
GO
With the above code successfully executed. Let us go ahead and try to insert some value in the table.
USE ReaOnlyDB
GO
INSERT INTO tbl_SQLAuth VALUES (1), (2)
GO
You will be greeted with the below error message inside SQL Server Management Studio.
Msg 3906, Level 16, State 1, Line 20
Failed to update database “ReaOnlyDB” because the database is read-only.
We have successfully replicated this error message. It is as easy as it gets. If you encounter this error message, we need to remove the ReadOnly attribute from our database. This can be achieved using the following TSQL:
USE [master]
GO
ALTER DATABASE [ReaOnlyDB] SET READ_WRITE WITH NO_WAIT
GO
As simple as it gets. Now the database is available for Write operations.
USE ReaOnlyDB
GO
INSERT INTO tbl_SQLAuth VALUES (1), (2)
GO
Now the insert statement would succeed without any errors. With the error message and resolution discussed. Let me give the cleanup script for this blog.
-- Clean up time
USE MASTER
GO
DROP DATABASE ReaOnlyDB
GO
As I wrap up this blog, would love to hear from you if you have every marked and used ReadOnly databases in your environments? What are the scenario’s you have used these options in databases? Or is this the first time you read about ReadOnly databases? Let me know.
Reference: Pinal Dave (https://blog.sqlauthority.com)
23 Comments. Leave new
Hi Pinal,
I agree — there isn’t much fuss with this error message because it tells you exactly what you need to do!
I’m curious, though, about how/when people use ‘read only’ for their databases. I hope there are some good comments about this, because I often learn so much from other peoples’ comments.
Regards,
Connie
I meant to say — I have never needed to use ‘read only’ for anything. At least so far!
Same here, I look forward to learning from comments of blog readers based on their experience.
We have in our company some databases mirrored for failover and in this case they stay as “Ready Only”. But i need to create a user to access and read these databases without create it on a production enviroment. Is it possible?
Sorry about the English, but it is because i am from Brazil and i am doing my best. =)
Thanks For Explanation,
Sure, welcome.
1 interesting scenario for read only that i’ve heard about is for providing quick acces to large amounts of “archived” data. By using partitions to have an active partition for current read/write and (possibly multiple) for read only archives ofthe same table. This enables fast reads for reports and looking up old values because locks dont get in the way of the current partition in any way. Keep in mind though this is an example from a BIG organisation with loats and lots of data. For most cases this setup is just way too much.
Long back i had discussion with some of my friends and I remember hearing that locks are not taken in read-only database. I didn’t get time to verify that.
SELECT
Sorry, but I would have sent them to here:
Funny … my blog comes as first on lmgtfy
Riddle me this, what is one to do if they’re using db mirroring?
The query you provided yields the following error:
Msg 1468, Level 16, State 3, Line 1
The operation cannot be performed on database “DB_NAME” because it is involved in a database mirroring session or an availability group. Some operations are not allowed on a database that is participating in a database mirroring session or in an availability group.
Msg 5069, Level 16, State 1, Line 1
ALTER DATABASE statement failed.
This solution is great in a perfect world, but in my world not so much.. We have read_only (standby) copies of our production databases on a separate server. So this will not work there.
Please how to solve this Error:
Failed to update database “msdb” because the database is read-only.
Could not insert a backup or restore history/detail record in the msdb database. This may indicate a problem with the msdb database. The backup/restore operation was still successful.
Processed 312 pages for database ‘My_DB’
when backup database in SQL Express using C#
This may work well if your dB is actually set to “read-only” at the time of the error message. However, what is the issue if your dB is actually set to “read-write” but you still get this error trying to generate a snapshot of articles from said dB? It’s not so straight forward a fix then. Do you have any solution?
This message can also occur when using SQL Replication. If a Subscriber is one or several servers in an Availability Group, and you failover that AG, the Distribution Agent is still trying to write to the original replica in the AG and not the listener. Pinal, you could write a quick article on how to resolve this issue. Regards, Mark.
Hi Pinal,
Thank you so much for great solution.
But here in my case it’s completely different.
When I tried to create the SQL login on the secondary server(Log Shipping) the “Secondary_DB” is in read_only mode. I can’t change the read write mode as you explained.
Can you please suggest me the other way? I got the same error number and message.
Thank you.
You cannot update a database on the secondary server. I had the same error when trying to change the permissions to a database for a user on the secondary replica of an Availability Group. Also, Jobs that attempt to update a database on a secondary replica will also fail with the “database is read-only” error.
I think what people are missing here is that your database could be in an always on availability group. I have seen this error when the primary fails over to the secondary. In that case I had to connect to the secondary server to do my update. It would be a very bad decision to change the database from read only in that case.
A distributed Always On group (DAG) configuration would use read-only databases. This error is a perfect example of where an alias/unity name was not properly implemented to point the workstation(s)/applications to the primary database server. You would not want to change the permission in this situation but rather fix the actual issue instead. (A normal AlwaysOn AG would get around this by using the SQL listener name to point to the primary node. A DAG uses two SQL listeners so an alias/unity name is needed to point to the primary group.
Just wondering what one would do if they had the same error in a SQL Server 2016 Enterprise AlwaysOn Availability Group? When the primary fails over to a secondary replica, I get the failed to update database error when writing to the database through an ODBC connection.
I got this error when trying to backup one of my databases that was read-only. I did a backup of one of my databases and restored it with another name to have a “before” copy while i was testing. I set the “before” copy to read-only (both are simple recovery mode). Over night our nightly backup routine tried to back it up and it failed with the 3906 message (followed by a 3013 – Backup Database is terminating abnormally). Backup of read-only databases is possible so i don’t know why this error is occurring.
my databases are in Availability group, And i made secondary as read-write mode, even it is giving same error.