none
Error executing SELECT from sys.elastic_pool_resource_stats

    Question

  • The following has been observed in a development elastic pool for more than 24 hrs. 

    When executing the following via SSMS attached to my Azure server,

    SELECT start_time, end_time,     
      (SELECT Max(v)     
    FROM (VALUES (avg_cpu_percent), (avg_data_io_percent), (avg_log_write_percent)) AS value(v)) AS [avg_DTU_percent]   
    FROM sys.elastic_pool_resource_stats  

    I receive the following error:

    Msg 40197, Level 20, State 2, Line 1
    The service has encountered an error processing your request. Please try again. Error code 9100.

    At https://docs.microsoft.com/en-us/azure/sql-database/sql-database-develop-error-messages (for 40197)

    I read "You receive this error when the service is down due to software or hardware upgrades, hardware failures, or any other failover problems. The error code (%d) embedded within the message of error 40197 provides additional information about the kind of failure or failover that occurred. Some examples of the error codes are embedded within the message of error 40197 are 40020, 40143, 40166, and 40540."

    I cannot find any information on the 9100 error code.  Any suggestions would be appreciated.  In the absence of anything else I can always open low-level case.

    Wednesday, January 3, 2018 3:57 PM

All replies

  • Hello,

    You can query that DMV from the master database of the logical server. Could you try running it from the master database?


    Hope this helps.



    Regards,

    Alberto Morillo
    SQLCoffee.com


    Wednesday, January 3, 2018 6:27 PM
  • Hi Alberto,

    I have been executing from master (master is a must as executing from within one of our customer databases generates an 'invalid object name...' error).  We have five development pools and it executes as expected within four of them.  This is the first time I have seen this specific behavior over the months I have been using it.

    jl

    Wednesday, January 3, 2018 6:34 PM
  • Hello,

    Let me share this and try to find some help.


    Thank for your patience.

    Regards,

    Alberto Morillo
    SQLCoffee.com


    Wednesday, January 3, 2018 6:45 PM
  • Hello,

    Could you please let me know if you see anything that grabs your attention on Resource Health?

    https://azure.microsoft.com/en-us/blog/resource-health-exposes-historical-health/



    Thank you in advance.



    Regards,

    Alberto Morillo
    SQLCoffee.com

    Wednesday, January 3, 2018 7:24 PM
  • Hi Alberto,

    I can select my subscription in the Subscription type drop-down, but Elastic Pool is not an option under the Resource type drop-down.

    jl

    Wednesday, January 3, 2018 7:37 PM
  • Hello,

    I am trying to get some help. Thank you for your patience.



    Regards,

    Alberto Morillo
    SQLCoffee.com

    Wednesday, January 3, 2018 8:01 PM
  • Hello,

    SQL Azure teams says Msg 40197 should be transient one. If this is persisting SQL Azure team recommends to put this in the hands of Azure Support for further investigation. Please open a support case.



    Hope this helps.



    Regards,

    Alberto Morillo
    SQLCoffee.com

    Wednesday, January 3, 2018 11:30 PM
  • Hi Alberto,

    I actually opened a 'C' level case on this issue.  After a couple of  weeks (I ran my query at least once per day during that time) it was corrected but candidly am not certain what caused things to return to the previous (and expected) behavior. 

    jl

    Tuesday, January 23, 2018 4:05 PM