Lesson Learned #466:Managing SQL Server Workloads:Resolving Error Msg 10928 with DAC and SQL Queries

[Jose_Manuel_Jurado]

Dealing with SQL Server's error Msg 10928 is critical for database administrators. This error, indicating that the request limit for the database has been reached, can halt operations.

Recently, a customer reported encountering this error: "Msg 10928, Level 20, State 1, Line 1 Resource ID: 1. The request limit for the database is 60 and has been reached. See 'https://docs.microsoft.com/azure/azure-sql/database/resource-limits-logical-server' for assistance" This situation has prevented them from identifying the application responsible for this overload, consequently disrupting connectivity with other applications.

Understanding SQL Server Error Msg 10928

• This error indicates that the request limit for the database has been reached.
• The error message typically shows up as "Msg 10928, Level 20, State 1, Line 1 Resource ID: 1. The request limit for the database is 60 and has been reached."
• It halts operations because the database can no longer process additional requests.

If the situation is permanent and ongoing and we need to identify the application name or the requests that are causing the problem to recover the service an alternative is to connect using DAC 

Immediate Steps to Identify the Issue

1. Use Dedicated Administrator Connection (DAC): Connect to the server using DAC via SQLCMD. This can be done using the command sqlcmd -S admin:yourservername.database.windows.net -U [username] -P [password] -d dbname.
2. Execute Query to Identify Problematic Applications:
   • Once connected, run the query SELECT program_name, COUNT(*) FROM sys.dm_exec_sessions GROUP BY program_name.
   • This query will help identify which applications are consuming the most connections or requests.

We are going to using  Dedicated Administrator Connection (DAC) that allows us an additional connection that is not affecting by this error 19028. 

Resolution and Management

• After identifying the applications causing the high request volume, consider closing non-critical applications to free up resources.
• This step is a temporary measure to allow for a more thorough analysis of connection usage and better database management.

Further Steps and Information

• Reviewing the Azure SQL Database Resource Limits at the link provided in the error message (https://docs.microsoft.com/azure/azure-sql/database/resource-limits-logical-server) can provide more insight into managing and configuring resource limits.
• It's crucial for database administrators to regularly monitor and manage the request loads on their databases to avoid such errors.

This approach will help your customer identify the source of the issue and take necessary steps to resolve it while maintaining optimal database operation.

Enjoy!