none
Master DB - SP_WHO2

    Question

  • We are currently running always on availability groups on a 3 node cluster. On one of the servers, when I connect to the Master database and try and run SP_WHO2, it seems to be ignoring that it's running the query against MASTER and trying to use one of the databases in the availability group so not accessible.

    Msg 978, Level 14, State 1, Procedure sp_who2, Line 94
    The target database ('XXXXX') 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.

    The AG's are set to read intent only but I would of thought that as this was the master database, and I've connected to this, it would run SP_WHO2 regardless.

    I've checked my connection strings and it is pointing to the default db and even if I type or select master db I get the same message. 

    This is SQL Server 2014 

    Tuesday, September 11, 2018 2:31 PM

All replies

  • Does the login have the same default db on the problem server as on the servers where it works?
    Tuesday, September 11, 2018 2:38 PM
  • I've just checked and yes they do. 

    The bar at the bottom shows me as being connected to the master database so why when executing SP_WHO2 it tries to use a user database participating in an always on availability group is beyond me. 

    Tuesday, September 11, 2018 2:59 PM
  • If you selected "master" in the connection dialog and connected to the secondary replica directly (not using listener), it should allow you to run SP_WHO2. Even if the login's default DB is an AG DB (on secondary), since you changed it to master DB context at the connection time, it should allow running sp_who2.  

    What Service Pack are you on?


    Please remember to click "Mark as Answer" if my response answered your question or click "Vote as helpful" if it helped you in any way.

    Tuesday, September 11, 2018 3:08 PM
  • So miraculously today it is now working as I would expect. Nothing major has changed as far as I can see. I'm 2014 Enterprise SP2 CU10-GDR

    Wednesday, September 12, 2018 8:20 AM
  • Hi KirkHughes,

    Thanks for your reply. We are glad to hear that you have solved your problem. Please kindly mark the helpful reply as answer. By doing so, it will benefit all community members who are having this similar issue.  Your contribution is highly appreciated.

     

    Best Regards,

    Rachel

     


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, September 13, 2018 1:26 AM