Error 40197 and 701 While Checking DB Properties in Elastic Pool

Question

Experiencing a timeout error when attempting to check database properties in an elastic pool. What could be the cause of errors 40197 and 701, and how can they be resolved?

Answer 1

Error 40197 indicates that the service has encountered an error processing your request, which may occur due to software or hardware upgrades, hardware failures, or other failover problems. This error suggests that the service is temporarily unavailable, and you should try reconnecting to the SQL Database until the resources are available again.

Error 701, while not explicitly detailed in the provided context, typically relates to resource limitations, indicating that the server is currently too busy to process the request. This can occur when the total number of concurrent workers across all databases in the elastic pool exceeds the pool limit.

To resolve these errors, you can try the following steps:

1. Retry the Connection: For error 40197, implement logic in your application to catch this error and automatically retry the connection after a brief wait.
2. Check Resource Limits: For error 701, consider increasing the DTUs or vCores of the elastic pool to allow for more concurrent requests, or reduce the number of databases or their resource usage in the pool.
3. Monitor Resource Usage: Keep an eye on the resource usage of your databases to ensure they are within the limits set for the elastic pool.

---

References:

• Troubleshoot connectivity issues and other errors
• Troubleshoot connectivity issues and other errors

Answer 2

Hi Biren Borad,

Welcome to Microsoft Q&A forum.

As I understand, you are getting Error 40197 and 701 While Checking DB Properties in Elastic Pool.

As per Microsoft Standard documentation on error codes, Error code 701 with error message signifies"There is insufficient system memory in resource pool '%ls' to run this query."

If you encounter out of memory errors, review sys.dm_os_out_of_memory_events. This view includes information on the predicted out of memory cause, determined by a heuristic algorithm and is provided with a finite degree of confidence.

SELECT * FROM sys.dm_os_out_of_memory_events ORDER BY event_time DESC;

If out of memory errors persist in Azure SQL Database, consider at least temporarily increasing the service level objective of the database in the Azure portal.

If out of memory errors persist, use the following queries to look for unusually high query memory grants that might contribute to an insufficient memory condition. Run the following example queries in the database that experienced the error (not in the master database of the Azure SQL logical server).

Other methods could be DMVs and Query store as illustrated here in detail: https://learn.microsoft.com/en-us/azure/azure-sql/database/troubleshoot-memory-errors-issues?view=azuresql

Another Error 40197, we 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. Reconnecting automatically connects you to a healthy copy of your database.

Your application must catch error 40197, log the embedded error code (%d) within the message for troubleshooting, and try reconnecting to SQL Database until the resources are available, and your connection is established again.

For more information, see Transient errors.

Let us know if your pool current configuration details and if still having issue.

Awaiting your reply.

Thanks

Answer 3

@Biren Borad
Thank you for the question and for using Microsoft Q&A platform.

It sounds like you are experiencing a timeout error when attempting to check database properties in an elastic pool, and you are seeing error codes 40197 and 701. These errors can occur for a variety of reasons, but they are typically related to resource constraints or connectivity issues.

1. Error code 40197 typically indicates that the service is busy and unable to process the request within the allotted time. This can occur if the elastic pool is under heavy load or if there are resource constraints that are preventing the request from being processed.
2. Error code 701 typically indicates that there is insufficient memory available to complete the request. This can occur if the elastic pool is running low on memory or if there are other processes running on the same server that are consuming a large amount of memory.

To resolve these errors, you may need to adjust the resource allocation for the elastic pool or optimize the queries that are being executed. You can also try increasing the timeout value for the request to give the service more time to process the request.

Please refer to this document for more help:
https://learn.microsoft.com/en-us/azure/azure-sql/database/troubleshoot-common-errors-issues?view=azuresql#transient-fault-error-messages-40197-40613-and-others

Hope this helps. Do let us know if you have any further queries.

---

If this answers your query, do click Accept Answer and Yes for was this answer helpful. And, if you have any further query do let us know.