How to Fix: Azure SQL Database Connection Timeout

The Scenario

It’s 2:00 AM on a Saturday and your production web app, deployed on Azure App Service, starts returning 500 errors. Monitoring shows a sudden spike in “The timeout period elapsed prior to obtaining a connection from the pool” errors when querying an Azure SQL Database. Your users are seeing “Something went wrong” pages, and the on-call pager is firing off.

Symptoms

• Application logs show: System.Data.SqlClient.SqlException (0x80131904): The timeout period elapsed prior to obtaining a connection from the pool. This may have occurred because all pooled connections were in use and max pool size was reached.
• Azure portal shows DTU consumption at 100% for the database.
• Application response times increase from ~50ms to over 30 seconds before failing.

Root Cause

The connection pool in your application (default max size = 100) is exhausted because database queries are taking too long to execute. This is typically caused by a sudden surge in traffic, a slow query that locks resources, or the database hitting its DTU or vCore limit. When connections are held open waiting for the database, new requests queue up until they hit the 30-second default timeout.

Resolution (Step-by-Step)

1. Check Azure SQL Database DTU/vCore usage in the Azure portal:

   • Navigate to your SQL server → Monitoring → Metrics
   • Add metric: dtu_consumption_percent or cpu_percent
   • If at 100%, you’re throttled. Scale up your tier (e.g., from S2 to S3) or enable auto-scaling if using vCore model.

2. Identify and kill blocking queries:

   -- Run in SSMS or Azure Data Studio connected to the database
   SELECT blocking_session_id, wait_type, wait_time, session_id
   FROM sys.dm_exec_requests
   WHERE blocking_session_id > 0;
   
   -- Kill the blocking session (replace <session_id>)
   KILL <session_id>;

3. Increase connection pool size and timeout in app code (example for .NET):

   // In your connection string
   "Server=tcp:<server>.database.windows.net;Database=<db>;User ID=<user>;Password=<pwd>;Trusted_Connection=False;Encrypt=True;Connection Timeout=30;Max Pool Size=200;"

4. Add retry logic with exponential backoff:

   using Polly;
   
   var retryPolicy = Policy
       .Handle<SqlException>(ex => ex.Number == -2) // Timeout exception
       .WaitAndRetry(3, retryAttempt => TimeSpan.FromSeconds(Math.Pow(2, retryAttempt)));
   
   retryPolicy.Execute(() => {
       // Your database call here
   });

5. Optimize slow queries:

   • Enable Query Store in Azure SQL and find high-resource queries:

   ALTER DATABASE <db> SET QUERY_STORE = ON;

   • Check top resource consumers:

   SELECT TOP 10 query_id, avg_cpu_time, avg_logical_io_reads
   FROM sys.query_store_query_stats
   ORDER BY avg_cpu_time DESC;

   • Add missing indexes or rewrite queries.

Why This Sometimes Doesn’t Work

If you scale up the database but still see timeouts, the issue is likely a deadlock or a transaction that’s left open without commit/rollback. Also, connection pooling problems can persist if you’re not disposing of connections properly — always wrap SqlConnection in a using block. In rare cases, Azure SQL has a hard limit on concurrent connections (e.g., 100 for S2 tier) that scaling to the next tier won’t help — you need to move to a higher service tier (e.g., Standard to Premium) or implement connection multiplexing.

Verification

Run a test query with a short timeout to confirm the database is responsive:

sqlcmd -S <server>.database.windows.net -d <db> -U <user> -P <pwd> -Q "SELECT 1" -t 5

Expected output: 1 returned within 5 seconds. Also check application logs for no more timeout errors after deploying the fix.

Common Follow-up Questions

Q: Can I increase the connection timeout beyond 30 seconds? Yes, but it’s a band-aid. Increase it in the connection string (e.g., Connection Timeout=60) but prioritize finding the root cause of slow queries.

Q: How do I monitor blocking queries in real-time? Use sys.dm_exec_requests and sys.dm_tran_locks DMVs. Set up an alert in Azure Monitor on the blocked_by_processes_count metric.

Q: Does Azure SQL have a connection limit per tier? Yes. For DTU-based tiers, S2 has 100 concurrent connections, S3 has 200. For vCore, it’s based on memory (e.g., 2 vCore = 200 connections). Check the official limits.

Prevent This in the Future

Set up proactive monitoring on Azure SQL DTU/cpu usage and connection pool exhaustion metrics. Configure alerts to fire at 80% usage to catch issues before they cause timeouts. Teams using Better Stack can consolidate these alerts with application logs and get paged only when it matters, reducing noise and speeding up incident response. Set up uptime monitoring and alerts with Better Stack