Getting Error 10928: The request limit for the database is 200 and has been reached?

Question

I am using an Azure SQL Server and getting Error 10928: The request limit for the database is 200 and has been reached. Can someone help me to understand what is causing the error?

Answer 1

@Susheel Bojjawar-MSFT

If you are experiencing this issue that means that you have reached a limit of concurrent workers allowed for that database on that service tier. Immediate recommendation is to scale database to a larger Service tier sufficient to handle workload. Please refer to the article - Resource limits for single databases using the vCore purchasing model to know the detailed resource limits for Azure SQL Database using the vCore purchasing model.

Here are few suggestions as a long-term solution to prevent that from happening:

1. Optimize queries to reduce the resource utilization of each query if the cause of increased worker utilization is due to contention for compute resources. For more information, see Query Tuning/Hinting.
2. Reduce the MAXDOP (maximum degree of parallelism) setting. Please refer to the documentation to configure MAXDOP.
3. Optimize query workload to reduce number of occurrences and duration of query blocking. To get more details on blocking sessions when this issue reoccurs, please use the query below.
   SELECT TOP 10 r.session_id,
   r.plan_handle,
   r.sql_handle,
   r.request_id,
   r.start_time,
   r.status,
   r.command,
   r.database_id,
   r.user_id,
   r.wait_type,
   r.wait_time,
   r.last_wait_type,
   r.wait_resource,
   r.total_elapsed_time,
   r.cpu_time,
   r.transaction_isolation_level,
   r.row_count,
   st.text
   FROM sys.dm_exec_requests r
   CROSS APPLY
   sys.dm_exec_sql_text(r.sql_handle) as st
   WHERE r.blocking_session_id = 0
   and r.session_id in (SELECT distinct(blocking_session_id) FROM sys.dm_exec_requests)
   GROUP BY r.session_id,r.plan_handle,r.sql_handle,r.request_id,r.start_time,r.status,r.command,r.database_id,r.user_id,
   r.wait_type,r.wait_time,r.last_wait_type,r.wait_resource,r.total_elapsed_time,r.cpu_time,r.transaction_isolation_level,r.row_count,st.text
   ORDER BY r.total_elapsed_time desc

Here are a few other suggestions:
• Tune and optimize your queries as this will help with request limit issues in future.
• Develop your own mechanism of governing the access to your databases. Block the Logins that are beyond the maximum capacity of the database you chose.
• Treat the exception that your application gets whenever the limits of Logins are reached and inform the user in a graceful way that there no more available connections at that moment, and then retry to connect in every so many seconds, until there is an available login connection.
• Increase the service level whenever necessary and decrease it back when the higher number of available logins is not necessary anymore (From a financial standpoint, this should cost you only a few extra cents, or few extra Dollars per hour during these times of extra capacity need).
• You can also update statistics and indexes on a regular basis outside of business hours to improve database performance. Link given below for reference.
How to maintain Azure SQL Indexes and Statistics - Microsoft Tech Community
• You can apply recommendations from portal to improve query performance. Link provided below for reference.

Regards
Navtej S