December 9, 2019 at 4:27 pm
SQL Server 2016 standard edition, with primary databases and one other server set up as secondary failover with always on availability group.
There were some issues over the weekend with an application that accesses the databases on this server, unbeknownst to me, they decided just to reboot the database server, which seemed to clear things up with the application.
This morning I was checking the databases, as I was going through the emails I was cc'd on it looked like there was permissions issues with tempdb for one of the databases. One of the databases will not display a properties window, giving the property spaceavailable is not available for database 'x'message.
I checked the ownership of the databases with a query to make sure it wasn't set to "unkown", and it is owned by a domain service account that owns another database (that I am available to see the properties of).
Doing some more reading of this (properties) error I see the easiest method to deal with this seems to be just to change the dbowner to sa then change it back. However I also read this can be an issue with failover databases with ownership changes not going over to the failover database.
Is this an issue I need to be concerned about, if I am changing the owner to sa then back to original owner?
Is there a better way to fix the unable to see properties window in this particular database?
thanks in advance.
December 10, 2019 at 5:10 pm
Thanks for posting your issue and hopefully someone will answer soon.
This is an automated bump to increase visibility of your question.
December 18, 2019 at 8:42 am
Glen_A. We always set the db owner to 'sa' on all replicated databases. However, the owner is not replicated from the Primary replica to the Secondaries so you will have to fail over to each replica in turn and set it locally.
December 18, 2019 at 7:02 pm
Thanks for the reply, I will take that into consideration and see if I can fit in a failover so I can change it on both nodes.
I am still mystified however, on why I cannot see the properties of just the one database, while I can see the properties on another that has the same db owner.
December 26, 2019 at 10:40 pm
Basically, fail over the AG to each replica and issue
ALTER AUTHORIZATION ON DATABASE::[AppLiveDb] TO [sa]
With regards to the properties window, maybe your AG properties are set to not allow reading of the secondary database copies? Personally, I enable "Readable Secondary" (not always read-only routing) on all replicas of the AG just so that I can issue queries against those copies without needing to fail over the AG.
December 29, 2019 at 2:14 pm
I don't think Glen_A will be able to enable readable secondary as they are using Standard Edition?
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply