0

I have a SQL 2017 mirroring setup and found a query has 2 plans in query store and I wanted to force the good plan which runs much faster. But when i force the query plan I get below error. Same error if I try to do it using Query store GUI.

Note : I am trying to do this on the PRINCIPAL in my DB mirroring setup with high safety automatic failover mode.

Msg 12446, Level 16, State 1, Procedure sp_query_store_force_plan, Line 1 [Batch Start Line 0] Query Store stored procedure is not available on readable secondary replica.

Even though Always On is enabled at the instance level, we are not using it. However, we are using DB mirroring with high safety automatic failover mode.

Why am I not allowed to force a plan on Principal DB and what is the workaround for this.

Improve this question
0

1 Answer 1

Reset to default
2

I'm unable to repro this, so it doesn't seem (on a basic setup and test) that there are any issues with QDS and Mirroring.

Most likely what is happening is that your connection is being redirected to the mirror. Please gather the data showing your connection is in fact using the principal, the execution, and the error, or a simple repro of the problem.

enter image description here

Improve this answer
3
  • Hi @Sean.. I am sure I was executing in the Principal. I dont think I will be able to open query store in mirror and look at the regressed queries in first place. I dont know wat was happening in my case, error message indicates it is trying to execute in secondary replica, I can take that as mirror db , because its a mirroring setup. I did a mirroring failover and failback overnight and forced the same plan in the same principal DB in the morning through GUI and it worked. God only knows wat is this buggy error.
    – udhayan dharmalingam
    Commented Feb 23, 2023 at 13:19
  • It's not a buggy error, it's clear your connection was going to the mirror.
    – Sean Gallardy
    Commented Feb 23, 2023 at 14:00
  • Why would it try to execute in mirror when I execute the query in PRINCIPAL DB. how come it worked on the same instance and same DB after a failover and failback. That makes me think it as a buggy error because I had faced buggy errors wrt to mirroring in the past as well, when adding a mirror DB through GUI it gives an error and adding through tsql works
    – udhayan dharmalingam
    Commented Feb 24, 2023 at 11:06

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.