0

Currently we have a process where any query running over 2 mins (only select) in primary database participating in db mirroring is killed, running every 5 mins.

As we have moved to Availability groups now secondary has become readable replica so users can run queries there coming with application_intent ='read only'

However we still need the job to kill long running query over 2 mins on primary replica as new users onboard there are always chances of them hitting primary database. How can i change my current job on both primary and secondary replica so that it only kills long running query on P replica and not readable without me changing job configuration every-time.

Thanks,

5
  • The concept discussed in the accepted answer of the linked duplicate is what you want! Essentially, you have the job running all the time on both servers, and the job checks to see if it's on the primary or not using the AG DMVs. Commented Mar 26, 2021 at 16:56
  • @JoshDarnell: Thanks but does it change when the other side is readable secondary. I was bit confused there and thought to check on best practices in case query needs to be killed Commented Mar 26, 2021 at 17:03
  • Your job will have 2 steps: 1 - check if primary (if then fail and end the job as success), 2 - kill your session. That way, it will only run step 2 on the primary and that will never affect the secondaries. By the way, that seems like an ugly workaround. Maybe the user should be train in some ways to prevent from those long running queries at first Commented Mar 26, 2021 at 17:36
  • @DominiqueBoucher: Well that is never stopping process to tune queries ;) This is just to prevent someone impacting the production database, Also should be worried about KILL as i heard AGs have connection string which anyways is gonna point to secondary and they cant connect to Primary? Commented Mar 26, 2021 at 17:40
  • Your job will not use the listener (which is pointing to primary or secondaries). It will run on each node independently and will execute the killing step only when the database has the primary role on that instance. P.S. Long running queries on secondaries can also impact performance on the primary (specially if you are running in sync mode) Commented Mar 26, 2021 at 18:01

0