1

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."

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.

  • 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 '17 at 3:26

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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