Error Code: 10928 – Resource Limit Exceeded

Summary Table

Background

Error code 10928 in Azure SQL Database indicates that the session count has reached its maximum allowed limit, causing resource throttling. This error often occurs when the SQL Database hits the limit of concurrent sessions (i.e., user connections) allowed for the specific pricing tier or service level.

Error Code Explanation

• Error Code: 10928
• Message: “Resource ID: <Resource_ID>. The request limit for the elastic pool or database has been reached. Please retry later.”
• This error occurs when the maximum concurrent sessions allowed for the database or elastic pool is exceeded.

Why This Issue Occurred

Azure SQL Database enforces resource limits based on the pricing tier (Basic, Standard, Premium, etc.). These tiers limit the number of concurrent connections to ensure optimal performance and resource allocation across tenants. When the session count exceeds the allowed threshold, new sessions will be rejected until some connections are closed.

Root Cause

• Max Concurrent Sessions Reached: Each service tier has a limit on the number of concurrent sessions or connections.
  • For example, in Basic tier, the max concurrent session limit might be 30.
  • In Standard tier (S1), the limit might be 60, and so on.
• Excessive Active Connections: Too many connections from applications, processes, or users can consume all available sessions.
• Misconfigured Connection Pooling: Improper connection pooling settings in your application could result in too many idle or unused connections being open at the same time.

Workarounds and Solutions

Step-by-Step Solution

Step 1: Understand the Database Service Tier Limits

First, check the Azure SQL Database service tier and the associated session limits. This will help you understand the maximum number of connections allowed.

Example:

• For the Basic tier, max concurrent sessions may be limited to 30.
• For Standard S2, the limit might be 120.
• For Premium P1, it could be higher (320 or more).

How to Check the Current Service Tier:

1. Go to the Azure Portal.
2. Navigate to SQL databases and select your database.
3. In the Overview tab, check the Pricing Tier and its limits.

If your database has reached the session limit for the current tier, you can either scale up to a higher tier or optimize the usage of existing sessions.

Step 2: Review Active Connections

Identify the number of active sessions to ensure that unnecessary or idle connections are closed. You can use the following query to check active sessions:

SELECT 
    session_id, 
    login_name, 
    host_name, 
    program_name, 
    status
FROM sys.dm_exec_sessions
WHERE database_id = DB_ID('<YourDatabaseName>');

This will return the active sessions along with their status.

• Action: If you find many inactive or idle connections, consider closing them to free up resources.

Step 3: Implement Connection Pooling in Your Application

Improper connection pooling can lead to too many open sessions, especially in web applications where a new connection is opened for every request but not properly closed.

Example of Connection Pooling in C# (ADO.NET)

string connectionString = "Server=tcp:<your_server>.database.windows.net,1433;Database=<your_database>;User ID=<your_user>@<your_server>;Password=<your_password>;Connection Timeout=30;Max Pool Size=100;";

using (SqlConnection connection = new SqlConnection(connectionString))
{
    connection.Open();
    // Execute your query here
}

Make sure you:

• Set the Max Pool Size to limit the number of open connections.
• Ensure connections are closed properly after each use (connection.Close()).

Step 4: Optimize Application Logic

In some cases, applications may leave sessions open longer than necessary. Here are some common optimization techniques:

1. Close Connections Immediately: Ensure that connections are not left open after queries have been executed.
2. Use Connection Pooling: This allows applications to reuse connections, reducing the overhead of opening/closing new connections.
3. Batch Queries: If multiple queries are being executed, consider batching them into fewer database hits.

Step 5: Scale Up the Database or Elastic Pool

If your application legitimately needs more concurrent sessions than the current tier allows, consider scaling up the database or elastic pool to a higher pricing tier.

Scaling up using the Azure Portal:

1. In the Azure portal, navigate to your SQL Database.
2. In the Overview section, click on Pricing tier.
3. Select a higher tier, such as upgrading from Standard to Premium or from S1 to S2.
4. Click Apply.

Scaling using PowerShell Example:

# Variables
$ResourceGroupName = "YourResourceGroup"
$ServerName = "YourServerName"
$DatabaseName = "YourDatabaseName"
$ServiceObjective = "S3"  # Scale up to Standard S3 or Premium

# Scale the database
Set-AzSqlDatabase -ResourceGroupName $ResourceGroupName -ServerName $ServerName -DatabaseName $DatabaseName -RequestedServiceObjectiveName $ServiceObjective

Step 6: Use Retry Logic

Since Azure SQL Database automatically handles load balancing and scaling, sometimes the error can resolve itself if you wait for a few seconds and retry the connection. Implement retry logic with exponential backoff to handle this situation.

Retry Logic Example in C#

int retryCount = 3;
int delay = 2000;  // 2 seconds delay

for (int i = 0; i < retryCount; i++)
{
    try
    {
        using (SqlConnection connection = new SqlConnection(connectionString))
        {
            connection.Open();
            // Execute query
        }
        break; // Exit the loop if connection succeeds
    }
    catch (SqlException ex) when (ex.Number == 10928)
    {
        // Log the exception (optional)
        Thread.Sleep(delay);  // Wait for delay before retrying
        delay *= 2;  // Exponential backoff
    }
}

Step 7: Monitor Resource Usage

Monitor the database’s resource utilization regularly to avoid hitting the limits.

1. Go to Monitor in the Azure portal.
2. Select Metrics under your SQL database resource.
3. Monitor metrics like CPU percentage, DTU usage, and connections count.
4. Set alerts to notify you if the database approaches the session limit.

Real-World Example Scenario

Scenario: An e-commerce website using Azure SQL Database on the Standard S1 tier receives an influx of traffic during a sale. Suddenly, customers experience slow responses, and the application throws Error Code 10928.

Step-by-Step Response:

1. Identify the Issue: Run the query to check the number of active sessions and find that the maximum session limit of 60 for the S1 tier is exceeded.
2. Apply Retry Logic: Implement retry logic with exponential backoff to handle transient connection issues.
3. Optimize Connections: Ensure connection pooling is used in the application code to avoid unnecessary session creation.
4. Scale Up: Use the Azure portal or PowerShell to scale the database from Standard S1 to Standard S3, increasing the maximum number of concurrent sessions to 240.
5. Monitor: Set alerts in Azure Monitor to notify when the session count approaches the limit again.

To resolve Error Code: 10928 (Resource Limit Exceeded) using SQL Server Management Studio (SSMS), follow these steps:

Step-by-Step Solution Using SSMS

Step 1: Check Active Sessions in SSMS

To identify active sessions and their status, use the following query in SSMS:

SELECT 
    session_id, 
    login_name, 
    host_name, 
    program_name, 
    status, 
    COUNT(*) OVER () AS active_sessions
FROM sys.dm_exec_sessions
WHERE database_id = DB_ID('<YourDatabaseName>');

• This query will show the active sessions and their associated logins.
• Action: If you see many idle or inactive sessions, you can decide to terminate some of them to free up resources.

Step 2: Terminate Unnecessary Sessions

If you identify any inactive or unnecessary sessions that you want to terminate, use the KILL command.

• To terminate a session, execute the following command:

KILL <session_id>;

• Replace <session_id> with the actual session ID from the previous query.
• Note: Be cautious while using the KILL command, as it will terminate the session, which could interrupt any ongoing processes for that session.

Step 3: Optimize Connection Pooling Settings

Ensure that the application is not leaving too many idle or unnecessary sessions open by optimizing connection pooling settings in your application code. In SSMS, you can simulate this behavior by:

1. Closing Connections Immediately After Queries: Make sure that once your query execution is complete, you close the connection to avoid idle sessions remaining open unnecessarily.

Step 4: Scale the Database Tier in SSMS

If your workload legitimately requires more sessions than the current service tier allows, you should scale up the database tier. You can do this in SSMS by running the following T-SQL command to change the service tier.

Example to Scale Up the Database Service Objective Using T-SQL

ALTER DATABASE [<YourDatabaseName>]
MODIFY (EDITION = 'Standard', SERVICE_OBJECTIVE = 'S3');

• In this example, we are scaling the database to the Standard S3 tier, which allows more concurrent sessions.
• You can replace 'Standard' with 'Premium' if your workload requires a premium tier.

Step 5: Monitor Resource Usage

You can monitor the resource usage directly in SSMS by querying the resource usage metrics. Use the following query to track resource usage, including CPU and session counts:

SELECT 
*
FROM sys.dm_db_resource_stats

This will give you a snapshot of the resource consumption over time, including active sessions. You can monitor if you are approaching session limits and take appropriate action.

Step 6: Implement Query and Connection Optimization

• Ensure your queries are efficient and that you don’t leave open connections unnecessarily. For example, closing transactions promptly, reducing long-running queries, and batching database requests can help.
• If your queries are resource-intensive, consider rewriting them to optimize performance and reduce session usage.

Step 7: Retry Logic in Application Code

While this step is typically applied in the application code, you can ensure that any queries running in SSMS that throw Error 10928 are retried manually if appropriate.

Scenario Example: Scaling Up the Database Using SSMS

Let’s say you’re working with an e-commerce database hosted in the Standard S1 tier, and during a peak sale, you encounter Error 10928 due to excessive concurrent sessions.

Steps in SSMS:

1. Identify Active Sessions:
   • Run the sys.dm_exec_sessions query to see the number of active connections.
   • If needed, terminate inactive sessions using the KILL command.
2. Scale Up the Database:
   • Run the ALTER DATABASE T-SQL command to upgrade the service tier from Standard S1 to Standard S3.
   • This provides more concurrent connections and helps avoid hitting the session limit.
3. Monitor Resource Usage:
   • Use the sys.dm_db_resource_stats query to monitor resource usage over time and ensure the database is not throttled due to session limits.
4. Application Side: Encourage developers to implement connection pooling and retry logic for better resource management in the application.