Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Administration
 Failed Over to Reader Node - Now Read Only

Author  Topic 

albertkohl
Aged Yak Warrior

740 Posts

Posted - 2014-04-23 : 09:53:39

so last night, during a REALLY big update, my server ran out of log space, an it really made sql mad. database moved into a not-syc state and any time i tried to even query it, i got errors. so since this was a database in ha, i planned on failing over w/ data loss (which was okay for this db) and then cleaning up my mess, and re-publishing to ha.

well long story short, i removed the db from the AG and now any time i try to write to the db, i get read-only errors. the db shows in sys tables that it's NOT read only, but it's like something is left behind from when it was a read-only replica that wont let me write to it.

i've tried this:
ALTER DATABASE mydb SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
ALTER DATABASE mydb SET READ_WRITE WITH NO_WAIT
ALTER DATABASE mydb SET multi_user WITH ROLLBACK IMMEDIATE;


which ran w/o issue, but this for example:
USE mydb
EXEC sp_changedbowner 'sa'

will get me:
Msg 3906, Level 16, State 1, Line 1
Failed to update database "mydb" because the database is read-only.

if i right-click the db in object explorer, i get:
Cannot show requested dialog.
------------------------------
Property Size is not available for Database '[mydb]'.
This property may not exist for this object, or may not be
retrievable due to insufficient access rights. (Microsoft.SqlServer.Smo)

any thoughts, ideas or advice would be appreciated.

albertkohl
Aged Yak Warrior

740 Posts

Posted - 2014-04-23 : 10:05:12
Never mind, reset the sql instance, and all appears to be well now.
Go to Top of Page
   

- Advertisement -