Had a client today asking about this situation. They were working away on a machine and suddenly they got the message “Failed to update the database because the database is read-only”.
- The user hadn’t changed anything that they were aware of.
- Based on the user’s permissions (ie: what they could see), everything in SSMS looked normal.
- When they checked the sys.databases view, the database showed MULTI_USER.
- There was enough disk space.
- Folder permissions had not changed.
- The user was puzzled.
The issue was caused by the database being part of an availability group, and the AG had failed over. So suddenly, the user was connected to the replica database, not the primary. This is why the database said it was read-only.
What the user should have done was to have connected to the AG listener in the first place, not to the server name. Then when failover occurs, the listener would follow the primary server.
I think this error message is confusing. I’d really prefer that the system tell you that you are now not connected to the primary replica.
Dear Greg,
I just want to let you know that this article saved me today! Thank you so much for posting.
You are most welcome !
Hello Greg,
You article just saved me hours of troubleshooting.
Thank you
Really glad to hear it. That's why I write them.
Greg,
This is happening to me in a production application using C# Entity Framework, is there anything I can do that would stop it from happening there?
Hi Joe, Is the database in an availability group or using mirroring? If it's in an AG, are you connecting to the listener instead of the server?
Hi Greg,
I know this article was published a few years ago but I have this problem. The application is pointing to the listener and it's still getting the same error message.
I even tested it using SSMS pointing to listener and still the same. Any thoughts what I can do to resolve this issue?
Thank you in advane,
Mahsa
Hi Mahsa, sounds like the failover/listener isn't working properly. Start by finding out which server the listener is pointing to at present.
Thank you bro!
You are most welcome