2

I am trying to capture SQL query plans using sp_whosisactive on a alwayson secondary replica db server and getting the error below in the query_plan column. This happens when I run the code below as a sql job. However, when I have my SSMS connection set to use APPLICATIONINTENT = READONLY and execute it manually it stores the execution plan for the queries running on the server.

How do we set APPLICATIONINTENT = READONLY for SQL Agent jobs running on secondary readonly replicas. Thanks!

EXEC dbo.sp_WhoIsActive @get_plans = 1, @destination_table = 'dbo.WIA_Output'

error message="The target database ('xyz') is in an availability group and is currently accessible for connections when the application intent is set to read only. For more information about application intent, see SQL Server Books Online."

Improve this question

1 Answer 1

Reset to default
3

Since you're running sp_WhoIsActive, which looks at all running queries on the server, you can run it from any database. Just choose your database context as master or tempdb in your Agent job step.

Improve this answer
1
  • Yes,the sql job is running under master db. The issue occurs when the job runs it captures the output of sp_whoisactive and stores it in dbo.WIA_Output table. Within this table there is a column - query_plan where instead of the query plan, it inserts the error (above), so it seems that the sql job should be run with applicationintent = readonly setting but I dont know how to set that up. All other columns (sql_text etc) are populated properly except for query_plan. Thanks!
    – miledba
    Feb 12, 2017 at 3:26

Your Answer

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

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