Seconds of Downtime, Hours of Confusion: Handling Transient Azure SQL Disconnects
A database can recover in seconds while an application remains unavailable for much longer. Brief connectivity interruptions are expected failure modes that cloud applications should be designed to tolerate. With Azure SQL Database, transient connectivity errors can occur during short-lived service reconfiguration events. Microsoft notes that many such reconfiguration events complete in less than 60 seconds and recommends that applications implement retry logic rather than immediately surfacing transient failures to users. But the database interruption is only part of the story. An application might continue experiencing problems because of: Failed connections. Lost in-flight operations. Retry behavior. Connection-pool recovery. Authentication or token issues. Queued requests. Application instances retrying simultaneously. Backlogs that must drain after connectivity returns. This leads to an important troubleshooting principle: Database recovery and application recovery are separate milestones. Understanding that difference can turn a confusing availability incident into a defensible timeline and, more importantly, a more resilient application design. 1. Transient Does Not Mean Harmless A transient interruption is temporary, but its application impact doesn't have to be. Consider a simplified timeline: Time Event T0 Application healthy T1 Database connectivity is interrupted T2 Database connectivity becomes available again T3 Applications reconnect T4 Request backlog begins draining T5 Application returns to normal The actual database connectivity event is: T1 → T2 But users might experience impact across: T1 → T5 Calling the entire T1-to-T5 period a continuous database outage would be inaccurate unless evidence proves that the database itself remained unavailable. Instead, investigate the periods separately. 2. Build a Three-Layer Timeline A good post-incident investigation should correlate three timelines. Layer 1: Database/connectivity behavior Capture: Connection failures. Successful reconnections. Relevant Azure monitoring signals. Azure Service Health information when applicable. Database resource behavior. Layer 2: Client connectivity Capture: SQL exception number. Exception timestamp in UTC. Connection timeout. Retry attempts. Retry delays. Authentication failures. Network errors. Azure SQL troubleshooting guidance recommends logging failure timestamps and notes that SQL Database can return tracing information helpful during connectivity investigations. Layer 3: Application recovery Capture: Application health. Request failures. Queue depth. Retry volume. Connection-pool recovery. Backlog drain time. Time when users actually recovered. The timelines might reveal: Database connectivity restored │ ▼ Applications reconnect │ ▼ Backlog continues │ ▼ Application fully recovered That distinction is essential. 3. Classify the Failure Before Retrying It Not every SQL connectivity error means the same thing. Classify the symptom into categories such as: Transient availability/connectivity failure. Authentication failure. Token-related failure. Network-path problem. DNS problem. Firewall/configuration issue. Resource-governance condition. Application timeout. Long application recovery after SQL connectivity returns. Azure SQL Database documents multiple connectivity failure categories, including transient fault errors such as 40197 and 40613, authentication failures such as 18456, network termination conditions, firewall issues, and connection timeouts. The classification matters because: Not every failure should be retried in the same way. 4. Use Azure SQL Connectivity History Carefully Azure SQL Database exposes connectivity-event information through system views in the logical server's master database. One useful view is: sys.database_connection_stats It provides aggregated connection success and failure information in five-minute intervals. Connect to the logical server's master database and run: DECLARE @StartTime datetime2 = DATEADD(HOUR, -2, SYSUTCDATETIME()); SELECT database_name, start_time, end_time, success_count, total_failure_count, connection_failure_count FROM sys.database_connection_stats WHERE start_time >= @StartTime ORDER BY start_time DESC; This can help answer: Did connection failures occur? Which database was affected? In which five-minute interval? Were successful connections also occurring? But there's an important limitation. This is aggregated historical information, not a second-by-second outage trace. The data can also arrive later, with Microsoft documenting that it can take up to 24 hours for all information to appear. Therefore, don't use one five-minute bucket containing failures to claim: "The database was unavailable for five minutes." The evidence only tells you connection failures were recorded during that interval. 5. Look Deeper With sys.event_log For additional connectivity-event information, Azure SQL Database exposes: sys.event_log This view records successful database connections and connection failures and is intended to help track and troubleshoot Azure SQL Database connectivity activity. Again, connect to master. DECLARE @StartTime datetime2 = DATEADD(HOUR, -2, SYSUTCDATETIME()); SELECT database_name, start_time, end_time, event_category, event_type, event_subtype, event_subtype_desc, severity, event_count, description FROM sys.event_log WHERE start_time >= @StartTime AND event_category = N'connectivity' ORDER BY start_time DESC; Useful columns include: start_time end_time event_category event_type event_subtype_desc severity event_count description Microsoft cautions that querying sys.event_log can consume significant resources on logical servers with many databases or high login volumes, so keep the time range and other filters appropriately narrow. 6. Correlate SQL Evidence With Application Errors Now correlate the Azure SQL evidence with client telemetry. For every important application-side SQL exception, capture: UTC timestamp. SQL error number. Error message. Application instance. Operation. Retry attempt. Retry delay. Final outcome. Correlation or request ID when available. For example: UTC Layer Observation 10:00:02 Application SQL connection exception 10:00:04 Application Retry 1 fails 10:00:08 Application Retry 2 fails 10:00:15 SQL Connectivity succeeds 10:00:18 Application New connection succeeds 10:01:30 Application Queue still processing 10:05:00 Application Normal service restored Now you can separate: Connectivity interruption. Reconnection period. Application recovery period. That's substantially more useful than describing the full period as: "SQL was down." 7. Understand Connectivity and Reconfiguration Applications connect to the Azure SQL Database gateway, after which traffic is handled according to the effective connection policy and directed to the appropriate database environment. In a managed database service, applications must be designed for transient connectivity interruption. Microsoft specifically describes transient faults as conditions whose underlying cause resolves itself and notes that service reconfiguration can temporarily affect database connectivity. The application therefore shouldn't depend on one connection remaining valid indefinitely. Design for: Connections occasionally being terminated. New connection attempts occasionally failing. In-flight work requiring recovery. DNS and network paths needing to remain correctly configured. Authentication credentials or tokens remaining valid. Retry behavior that allows the system time to recover. Avoid guessing at undocumented platform internals when investigating an event. If evidence establishes only: "Connections failed briefly." Don't convert that into an unsupported explanation of why the platform changed state. 8. Retry Smartly, Not Aggressively Retry logic is essential for cloud applications that connect to Azure SQL Database. Microsoft explicitly recommends retry logic for transient connection failures. But a retry strategy needs boundaries. A good strategy considers: Number of attempts. Delay between attempts. Increasing delay. Random jitter. Maximum interval. Total retry budget. Whether the operation can safely be repeated. A bad strategy looks like: Failure ↓ Retry immediately ↓ Failure ↓ Retry immediately ↓ Failure ↓ Retry immediately... Across hundreds of application instances, this behavior can make recovery harder. A better pattern is: Failure ↓ Wait + jitter ↓ Retry ↓ Longer wait + jitter ↓ Retry ↓ Stop after bounded attempts 9. Use Microsoft.Data.SqlClient Retry Logic For .NET applications using Microsoft.Data.SqlClient, configurable retry logic is available in version 3.0 and later. Microsoft documents providers for configuring connection and command retries. A connection retry provider can be configured like this: var options = new SqlRetryLogicOption() { NumberOfTries = 5, DeltaTime = TimeSpan.FromSeconds(1), MaxTimeInterval = TimeSpan.FromSeconds(20) }; SqlRetryLogicBaseProvider provider = SqlConfigurableRetryFactory.CreateExponentialRetryProvider(options); using var connection = new SqlConnection(connectionString); connection.RetryLogicProvider = provider; connection.Open(); With this configuration: NumberOfTries = 5 Allows one initial attempt and up to four retries. DeltaTime Influences the delay between attempts. MaxTimeInterval Caps an individual retry delay. The exponential provider adds randomized jitter to retry intervals. The actual values should be tested against your application's: Latency requirements. Failure budget. Traffic volume. Concurrency. User experience. They're not universal service recommendations. 10. Connection Retry and Command Retry Are Different This is one of the most important resiliency distinctions. Suppose this fails: connection.Open(); Retrying the connection after an appropriate delay is generally straightforward for a transient connection condition. But suppose this fails: UPDATE dbo.Orders SET ProcessingStatus = N'Completed' WHERE OrderID = @OrderID; The application now has a harder question: Did the UPDATE execute before the connection was lost? Blindly repeating the command isn't always safe. Microsoft's Azure SQL guidance recommends establishing a fresh connection after a transient command failure before retrying, and retry logic must correctly account for the transaction's outcome. 11. Idempotency Matters Different operations require different retry treatment. Read-only operation For example: SELECT OrderID, ProcessingStatus FROM dbo.Orders WHERE OrderID = @OrderID; Repeating a read is often relatively straightforward, subject to your application's consistency requirements. Update operation For example: UPDATE dbo.Orders SET ProcessingStatus = N'Completed' WHERE OrderID = @OrderID; The client must determine whether repeating this operation is safe. Insert operation Consider: INSERT dbo.PaymentRequests ( RequestID, AccountID, Amount ) VALUES ( @RequestID, @AccountID, @Amount ); If the connection disappears after the server commits but before the client receives confirmation, an unconditional retry could create duplicate business activity unless the application and schema provide an idempotency mechanism. For example, a unique request identifier can make duplicate detection possible. The important principle is: Retryability is a business-operation property, not simply a SQL error property. 12. Transactions Need Special Treatment Be particularly careful with multi-statement transactions. Consider: BEGIN TRANSACTION; UPDATE dbo.Inventory SET Quantity = Quantity - @Quantity WHERE ProductID = @ProductID; INSERT dbo.ShipmentQueue ( RequestID, ProductID, Quantity ) VALUES ( @RequestID, @ProductID, @Quantity ); COMMIT TRANSACTION; If connectivity is interrupted, don't simply reconnect and retry the last statement. Your application needs a strategy that protects the integrity of the entire unit of work. Microsoft's retry documentation specifically cautions that if a transient failure invalidates an active transaction, the application should retry the entire transaction rather than only the failed statement. 13. Prevent the Retry Storm Imagine 500 application instances lose connectivity simultaneously. If every instance retries immediately: 500 requests fail ↓ 500 immediately retry ↓ Another synchronized wave ↓ Another synchronized wave Recovery traffic itself can become disruptive. Exponential backoff with jitter helps spread retries across time instead of concentrating them into synchronized bursts. The configurable retry provider in Microsoft.Data.SqlClient supports exponential retry behavior with randomized jitter. Also consider application-level resilience mechanisms such as: Circuit breakers. Back-pressure. Queueing. Controlled concurrency. Connection-pool recovery. Rate limiting. Load shedding where appropriate. The objective isn't: "Retry until SQL succeeds." It's: Recover gracefully without creating a second problem. 14. Don't Ignore the Network Path Not every SQL connection failure originates from the database. Azure SQL Database connectivity includes several components between the application and database, with connection behavior also influenced by the configured connection policy. Troubleshooting may need to include: DNS. Firewall configuration. Private endpoints. Network security rules. Client outbound connectivity. Connection policy. Authentication. Application host health. Azure SQL supports Redirect, Proxy, and Default connection-policy configurations depending on the environment and configuration. In Redirect mode, clients establish connections directly to the node hosting the database after the initial gateway interaction, which requires appropriate network access. This is another reason not to classify every connection exception as: "Azure SQL outage." 15. Test Resiliency Before an Incident Don't wait for a real platform event to discover whether your application's recovery logic works. Test controlled failure scenarios involving: New connections. Existing connections. Read-only requests. Writes. Multi-statement transactions. Multiple application instances. Queued workloads. Connection pools. Measure: Time to detect failure. First retry delay. Number of attempts. Time to establish a new connection. Failed user requests. Queue growth. Queue drain time. Full application recovery. The real success metric isn't merely: "Can I connect to the database again?" It's: "How quickly can the application return to normal service without compromising correctness?" 16. Write a Defensible Post-Incident Report A strong post-incident report separates facts, inferences, and unknowns. Confirmed facts Examples: Client connection failures were recorded during a specific UTC interval. Successful connections later resumed. Certain client instances continued failing after initial connectivity returned. Application backlog persisted beyond the connectivity event. Retry activity increased during recovery. Supported inference Example: Aggressive retry behavior likely contributed to slower application recovery. Use this classification only when application telemetry supports the relationship. Unknowns Examples: The exact internal platform operation responsible for a transient event. Whether an unrelated network event contributed. Whether a capacity change affected recovery. Don't fill gaps in telemetry with assumptions. 17. A Practical Decision Tree When an application reports an Azure SQL connectivity incident: 1. Is the database reachable now? No Check current Azure status and application connectivity. Validate networking, DNS, firewall, authentication, and Azure SQL availability. Yes Continue reconstructing the historical incident. 2. Did Azure SQL record connection failures? Yes Correlate: sys.event_log. sys.database_connection_stats. Application timestamps. Azure monitoring evidence. No or evidence isn't yet available Continue investigating application and network telemetry. Remember that historical connectivity data can arrive later. 3. Did the application retry? No Implement a bounded transient-fault handling strategy. Yes Evaluate: Retry count. Delay. Backoff. Jitter. Error classification. 4. Did many instances retry simultaneously? Yes Investigate: Retry synchronization. Circuit breaking. Back-pressure. Queue recovery. Connection pools. 5. Were writes or transactions interrupted? Yes Validate: Transaction outcome. Idempotency. Duplicate prevention. Whole-transaction retry behavior. 6. Did the application remain unhealthy after connections succeeded? Yes Measure application recovery separately from the original database connectivity event. Final Takeaway The hardest part of a transient Azure SQL event isn't always the interruption itself. Sometimes it's the application's response. A resilient architecture recognizes three separate timelines: Connectivity interruption Database/connectivity recovery Application recovery Azure SQL Database is designed as a cloud service where applications should expect and tolerate transient connectivity conditions. Microsoft recommends implementing retry logic rather than exposing appropriate transient failures directly to users. But resilient retry logic must be: Bounded. Delayed. Appropriately randomized. Error-aware. Transaction-aware. Idempotency-aware. Observable. And post-incident analysis should distinguish: What is confirmed. What is inferred. What remains unknown. Because: A brief database disconnect doesn't have to become a prolonged application outage. The objective isn't to design an application that never experiences a disconnected connection. The objective is to design an application that absorbs the interruption, recovers safely, and returns to normal operation predictably. Further reading Working with Transient Errors in Azure SQL Database Troubleshoot Common Connection Issues in Azure SQL Configure Retry Logic in Microsoft.Data.SqlClient Azure SQL Database Connectivity Architecture sys.event_log for Azure SQL Database sys.database_connection_stats for Azure SQL DatabaseLessons Learned #551: Azure SQL Connection Timeouts: Three Things to Check
An application starts reporting intermittent timeouts when connecting to Azure SQL Database. Some requests succeed, others fail, and a test from a developer’s laptop works perfectly. The database appears online, no recent deployment seems related, and the natural reaction is to ask: Is Azure SQL unavailable? Is the firewall blocking the connection? Should we increase the connection timeout? Should we change the driver or scale the database? Those are reasonable questions, but they may lead the investigation in the wrong direction. The most important lesson is simple: A timeout tells us how long the application waited. It does not tell us what the application was waiting for. Not every “SQL timeout” happens inside Azure SQL From the application’s point of view, opening a database connection may involve several operations: Resolving the server name. Reaching the SQL endpoint. Obtaining a Microsoft Entra access token. Waiting for an available pooled connection. Completing the SQL login. Executing the first command. When all these operations are reported through the same application method or log entry, it can look as though Azure SQL took thirty seconds to accept the connection. In reality, only part of that time may have been spent connecting to the database. In one anonymized support scenario, the application experienced problems mainly on its first connection. Network tests were successful and no corresponding SQL connection failure was identified. The investigation eventually showed that access-token acquisition was consuming a significant part of the available time. Increasing the SQL timeout or changing the firewall would not have addressed the real delay. Check 1: Capture the complete error and the exact time A screenshot containing only “Connection Timeout Expired” is rarely enough. Capture: The complete exception and inner exception. The operation being performed. The driver and version. The authentication method. The exact timestamp in UTC. Whether the issue affects every connection or only some of them. The wording around the timeout matters. For example, a timeout while obtaining a connection from the pool points toward the application’s pooling and concurrency behavior. A pre-login or TLS error belongs to a different investigation. A command timeout after the connection was established is usually a query-performance problem rather than a connection problem. Check 2: Measure the application timeline The application should record important operations separately. A simple timeline can completely change the investigation: 10:14:20.100 Token acquisition started 10:14:28.400 Token acquired 10:14:28.405 SQL connection started 10:14:29.050 SQL connection established The complete operation took almost nine seconds, but Azure SQL connection establishment took less than one second. Useful measurements include: Token-acquisition duration. Time waiting for a pooled connection. SQL connection-open duration. SQL command duration. Number of retry attempts. Applications using Microsoft Entra authentication must obtain an access token before authenticating to Azure SQL. Measuring that operation separately helps distinguish an identity delay from a database connectivity problem. This is particularly useful when the issue appears: On the first connection after startup. After a token expires. Only with Managed Identity or Workload Identity. Intermittently, while SQL authentication connections remain unaffected. Check 3: Test from the application environment A successful connection from a laptop does not validate the path used by an application running in: Azure App Service. Azure Functions. Azure Kubernetes Service. A virtual machine. An on-premises application server. A container or integration runtime. The laptop and the application may use different DNS servers, routes, firewalls, proxies and identities. Connectivity and DNS tests should therefore be performed from the environment that is actually failing. This becomes especially important when Private Endpoint is used. The application should continue connecting with: <server>.database.windows.net It should not use the Private Endpoint IP address or the privatelink.database.windows.net hostname directly. Direct login attempts using the private IP or the private-link FQDN fail; the normal logical-server FQDN must remain in the connection string. From the affected environment, confirm that: The expected DNS server answers the request. The server FQDN resolves to the expected private IP. The Private Endpoint connection is approved. The Private DNS zone is linked correctly. The resolved address is reachable through the intended route. A test from an unrelated machine is still useful for comparison, but it does not prove that the application path is healthy. Observed symptom Likely investigation area Timeout while obtaining a connection from the pool Application connection pooling Server name cannot be resolved DNS TCP connection to the endpoint cannot be established Network path, firewall or routing Error during the pre-login handshake TLS, driver, network interruption or pre-login processing Authentication or access-token error Microsoft Entra authentication, identity or token acquisition Timeout during the post-login phase Login completion, session initialization or server-side processing Execution or command timeout after connecting Query execution and database performance Avoid changing several things at once During a production incident, it is tempting to: Increase the timeout. Add firewall rules. Change the connection policy. Upgrade the driver. Restart the application. Clear connection pools. Applying several changes together makes it difficult to determine which one helped, and some may only hide the symptom. A better approach is to define one hypothesis: We believe DNS in the application environment is resolving the public endpoint instead of the Private Endpoint. Then define: The evidence supporting the hypothesis. One controlled change. The expected result. How the result will be measured. How the change will be reverted. Azure SQL supports Proxy and Redirect connection policies, which determine how traffic flows after reaching the Azure SQL gateway. The policy is configured for the logical server, so it should be verified before making firewall assumptions or changes. What should we collect before opening a support request? A small but precise evidence package can avoid several rounds of questions: Complete error and inner exception. Exact UTC timestamps. Application platform and location. Public or Private Endpoint. Server FQDN used by the application. Driver and version. Authentication method. Token, pool, connection and command durations. DNS result from the affected environment. Whether the issue is constant, intermittent or limited to the first connection. Recent application, network, identity or configuration changes.How to Get Database‑Wise Session Details in an Azure SQL Elastic Pool Using T‑SQL
When you run multiple databases inside an Azure SQL Database elastic pool, it’s common to hit questions like: Which database is using the most sessions right now? Are we getting close to the pool’s session limit? Which application(s) are opening connections? Is connection pooling configured correctly? The Azure portal can be helpful, but you don’t always have portal access—and even when you do, you may want a quick, scriptable approach you can run from SSMS / Azure Data Studio / sqlcmd. This post provides copy‑paste T‑SQL queries to: check pool‑level session pressure, list active sessions “by database”, summarize active session counts per database, and capture a connection inventory—and then ties it all back to one of the most common root causes of high session counts: connection pooling behavior in the application. What you should know up front (setting expectations) 1) Elastic pool DMVs give you pool context from inside any pooled database The DMV sys.dm_elastic_pool_resource_stats returns usage for the elastic pool that contains the current database, including concurrent session utilization, and it can be queried from any user database in the same elastic pool. 2) Connection/session DMVs can show pool‑wide connections (with sufficient permissions) Microsoft documentation notes you can use sys.dm_exec_connections to retrieve connection details—and if a database is in an elastic pool and you have sufficient permissions, the view returns the set of connections for all databases in the elastic pool. It also calls out sys.dm_exec_sessions as a companion DMV for session details. If you run the queries below and only see your own session, it typically indicates a permissions scope limitation (the documentation notes this behavior for DMV visibility). Quick “Which query should I run?” guide Are we close to pool session limits? → Query A Which database is busy right now (active work)? → Query B Give me a ranked list of active sessions per database → Query C Which apps/hosts/users are connecting? → Query D Query A — Check elastic pool session pressure (near real‑time) Run this in any user database in the elastic pool: SELECT TOP (60) end_time, avg_cpu_percent, avg_data_io_percent, avg_log_write_percent, max_worker_percent, max_session_percent, used_storage_percent FROM sys.dm_elastic_pool_resource_stats ORDER BY end_time DESC; How to interpret it max_session_percent tells you how close your pool is to its session limit (peak session utilization in the interval). This DMV is intended for real‑time monitoring and troubleshooting and retains data for ~40 minutes. Query B — Active sessions by database (best for “what’s happening right now?”) This query focuses on sessions that are currently executing requests, and attributes them to a database by using the request’s SQL context (DB_NAME(st.dbid)). The st.dbid approach is widely used in troubleshooting patterns to show the execution context database. SELECT DB_NAME(st.dbid) AS database_name, s.session_id, s.login_name, s.host_name, s.program_name, s.client_interface_name, c.net_transport, c.encrypt_option, c.auth_scheme, c.connect_time, s.login_time, r.status AS request_status, r.command, r.start_time, r.cpu_time, r.total_elapsed_time FROM sys.dm_exec_requests AS r JOIN sys.dm_exec_sessions AS s ON r.session_id = s.session_id JOIN sys.dm_exec_connections AS c ON c.session_id = s.session_id CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) AS st WHERE s.is_user_process = 1 AND r.session_id <> @@SPID ORDER BY database_name, r.cpu_time DESC; What customers typically use this for Identify which database has the most active work right now See which client program and host are responsible Spot heavy or long‑running requests using cpu_time and total_elapsed_time Query C — Count active sessions per database (simple ranked view) If you want a quick summary like “DB1 has 18 active sessions; DB2 has 5…” WITH active_pool_sessions AS ( SELECT DB_NAME(st.dbid) AS database_name, r.session_id FROM sys.dm_exec_requests AS r JOIN sys.dm_exec_sessions AS s ON r.session_id = s.session_id CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) AS st WHERE s.is_user_process = 1 ) SELECT database_name, COUNT(*) AS active_sessions FROM active_pool_sessions GROUP BY database_name ORDER BY active_sessions DESC; This is a great “top list” during incidents and uses the same execution context mapping pattern (st.dbid). Query D — Connection inventory (who is connected?) Use this when you suspect connection storms, too many open sessions, or connection pooling issues. SELECT c.session_id, c.net_transport, c.encrypt_option, c.auth_scheme, s.host_name, s.program_name, s.client_interface_name, s.login_name, s.original_login_name, c.connect_time, s.login_time FROM sys.dm_exec_connections AS c JOIN sys.dm_exec_sessions AS s ON c.session_id = s.session_id WHERE s.is_user_process = 1 ORDER BY c.connect_time DESC; Microsoft documentation provides this exact join pattern (connections + sessions) as the baseline way to retrieve connection metadata and notes elastic pool behavior when permissions allow. Connection pooling: the #1 reason session counts spike (and how to fix it) Now that you can see sessions and connections, here’s the most common “why”: connection pooling configuration and behavior in the application. What connection pooling is? Creating a new database connection includes several time‑consuming steps: establishing a physical channel, handshake, parsing the connection string, authenticating, and other checks. To reduce that overhead, ADO.NET uses connection pooling: when your application calls Open(), the pooler tries to reuse an existing physical connection; when your application calls Close()/Dispose, the connection is returned to the pool instead of being physically closed—ready for reuse on the next Open(). Important (and often misunderstood): pooling is client‑side, but it has a very real effect on how many concurrent sessions you consume in an elastic pool. Common pooling pitfalls that cause “too many sessions” 1) Connections are not being returned to the pool (connection leaks) Pooling relies on the application calling Close()/Dispose so the pooler can return the connection to the pool for reuse. If connections aren’t closed properly, the pool can’t reuse them, and your app may keep creating new ones. What it looks like in SQL: Query D shows a growing number of sessions from the same program/host over time. Practical fix: Ensure every DB usage pattern disposes the connection (e.g., using blocks in .NET). (General best practice; the pooling mechanism’s reliance on Close/return is documented.) 2) Pool fragmentation (you accidentally create multiple pools) ADO.NET keeps separate pools for different configurations. Connections are separated into pools by connection string and (when integrated security is used) by Windows identity. Pools can also vary based on transaction enlistment and credential instances. What this means: Small differences in connection strings across services/environments can create multiple pools—each with its own connections—so total sessions can be much higher than expected. What it looks like in SQL: Query D shows many sessions from the same overall application family but with slightly different connection contexts (different apps/services). Practical fix: Keep connection strings consistent across instances where possible (same keywords, same security settings, same app identity strategy). (The “separate pools by configuration” concept is documented.) 3) “Max pool size reached” (client-side pool exhaustion) mistaken for Azure SQL limits A Microsoft Tech Community troubleshooting post shows that if you set a small Max Pool Size, you can hit client-side errors such as: “Timeout expired… prior to obtaining a connection from the pool… all pooled connections were in use and max pool size was reached.” That is not the same as hitting an Azure SQL tier limit—it’s the application waiting because it can’t obtain a connection from its own pool. How to differentiate quickly If you see “max pool size reached” / “timeout obtaining connection from the pool” → client pooling pressure. If max_session_percent in Query A is consistently high → pool-level session pressure. 4) Holding connections open longer than necessary Even with pooling enabled, if your application opens a connection and then holds it while doing non-database work, those connections remain “in use” and can’t return to the pool—causing waits and more concurrent sessions under load. (This follows directly from the documented “Open returns a pooled connection; Close returns it to the pool” behavior.) What it looks like in SQL: Query D shows many sessions from the same application. Query B/C shows many active sessions tied to one database during spikes. Practical fix: Open connections as late as possible; close as early as possible around each DB unit of work. (General best practice derived from pooling mechanics.) A simple customer checklist (quick wins) Confirm every DB call closes/disposes the connection so it can return to the pool. Avoid varying connection strings unnecessarily (prevents pool fragmentation). If you see pool wait errors (“max pool size reached”), treat it as an application pooling signal first. Use the T‑SQL queries above to validate: pool pressure (Query A) busiest databases / active sessions (Query B/C) connection sources (Query D) Wrap‑up With the queries in this post, you can troubleshoot elastic pool session behavior without relying on the Azure portal: Query A: real-time pool session/worker pressure Query B/C: database-wise view of active workload (most actionable during incidents) Query D: connection inventory (great for pooling issues and connection storms) And when session counts spike, don’t overlook the application side: connection pooling behavior (leaks, fragmentation, pool sizing, and holding connections open) is one of the most common drivers.Azure PostgreSQL Lesson Learned #3: Fix FATAL: sorry, too many clients already
We encountered a support case involving Azure Database for PostgreSQL Flexible Server where the application started failing with connection errors. This blog explains the root cause, resolution steps, and best practices to prevent similar issues.Lesson Learned #519: Reusing Connections in Azure SQL DB: How Connection Pooling Caches Your Session
A few days ago, I was working on a case where a customer reported an unexpected behavior in their application: even after switching the connection policy from Proxy to Redirect, the connections were still using Proxy mode. After investigating, we found that the customer was using connection pooling, which caches connections for reuse. This meant that even after changing the connection policy, the existing connections continued using Proxy mode because they had already been established with that setting. The new policy would only apply to newly created connections, not the ones being reused from the pool. To confirm this, we ran a test using .NET and Microsoft.Data.SqlClient to analyze how the connection pool behaves and whether connections actually switch to Redirect mode when the policy changes. How Connection Pooling Works Connection pooling is designed to reuse existing database connections instead of creating a new one for every request. This improves performance by reducing latency and avoiding unnecessary authentication handshakes. However, once a connection is established, it is cached with the original settings, including: Connection policy (Proxy or Redirect) Authentication mode Connection encryption settings This means that if you change the connection policy but reuse a pooled connection, it will retain its original mode. The only way to apply the new policy is to create a new physical connection that does not come from the pool. Testing Connection Pooling Behavior For Testing the connection pooling behavior, I developed this small code in C# that basically, opens the connection, provides information about the port using and close the connection. Repeating this process 10000 times. The idea was to track active connections and check if the port and connection policy were changing after modifying the connection policy. Initially, I attemped to use netstat -ano to track active connections and monitor the local port used by each session. Unfortunately, in Azure SQL Database, local port information is not reported, making it difficult to confirm whether a connection was truly being reused at the OS level. Despite this limitation, by analyzing the session behavior and connection reuse patterns, we were able to reach a clear conclusion. using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Threading; using System.Threading.Tasks; using Microsoft.Data.SqlClient; namespace InfoConn { using System; using System.Data; using System.Diagnostics; using System.Text.RegularExpressions; using System.Threading; using Microsoft.Data.SqlClient; class Program { static void Main() { string connectionStringProxy = "Server=tcp:servername.database.windows.net,1433;Database=db1;User Id=user1;Password=..;Pooling=True;"; Console.WriteLine("Starting Connection Pooling Test"); for (int i = 0; i < 10000; i++) { using (SqlConnection conn = new SqlConnection(connectionStringProxy)) { conn.Open(); ShowConnectionDetails(conn, i); } Thread.Sleep(5000); } Console.WriteLine("Test complete."); } static void ShowConnectionDetails(SqlConnection conn, int attempt) { string query = "SELECT session_id, client_net_address, local_net_address, auth_scheme FROM sys.dm_exec_connections WHERE session_id = @@SPID;"; using (SqlCommand cmd = new SqlCommand(query, conn)) { using (SqlDataReader reader = cmd.ExecuteReader()) { while (reader.Read()) { Console.WriteLine($"[Attempt {attempt + 1}] Session ID: {reader["session_id"]}"); Console.WriteLine($"[Attempt {attempt + 1}] Client IP: {reader["client_net_address"]}"); Console.WriteLine($"[Attempt {attempt + 1}] Local IP: {reader["local_net_address"]}"); Console.WriteLine($"[Attempt {attempt + 1}] Auth Scheme: {reader["auth_scheme"]}"); } } } RetrievePortInformation(attempt); } static void RetrievePortInformation(int attempt) { try { int currentProcessId = Process.GetCurrentProcess().Id; Console.WriteLine($"[Attempt {attempt + 1}] PID: {currentProcessId}"); string netstatOutput = RunNetstatCommand(); var match = Regex.Match(netstatOutput, $@"\s*TCP\s*(\S+):(\d+)\s*(\S+):(\d+)\s*ESTABLISHED\s*{currentProcessId}"); if (match.Success) { string localAddress = match.Groups[1].Value; string localPort = match.Groups[2].Value; string remoteAddress = match.Groups[3].Value; string remotePort = match.Groups[4].Value; Console.WriteLine($"[Attempt {attempt + 1}] Local IP: {localAddress}"); Console.WriteLine($"[Attempt {attempt + 1}] Local Port: {localPort}"); Console.WriteLine($"[Attempt {attempt + 1}] Remote IP: {remoteAddress}"); Console.WriteLine($"[Attempt {attempt + 1}] Remote Port: {remotePort}"); } else { Console.WriteLine($"[Attempt {attempt + 1}] No active TCP connection found in netstat."); } } catch (Exception ex) { Console.WriteLine($"[Attempt {attempt + 1}] Error retrieving port info: {ex.Message}"); } } static string RunNetstatCommand() { using (Process netstatProcess = new Process()) { netstatProcess.StartInfo.FileName = "netstat"; netstatProcess.StartInfo.Arguments = "-ano"; netstatProcess.StartInfo.RedirectStandardOutput = true; netstatProcess.StartInfo.UseShellExecute = false; netstatProcess.StartInfo.CreateNoWindow = true; netstatProcess.Start(); string output = netstatProcess.StandardOutput.ReadToEnd(); netstatProcess.WaitForExit(); return output; } } } }Lesson Learned #455:Optimizing Connection Pooling for Application Workloads: Thread Contention
This week, I have been working on a support case where our client experienced a 5-second delay in database connectivity. They were measuring this using StopWatch. As they informed me, they were launching a series of processes, 50-100 concurrent processes to be precise. I would like to share the experience gained in understanding the reasons behind this 5-second delay.Lesson Learned #453:Optimizing Connection Pooling for Application Workloads: A single journey
In recent days, we have received several requests how to reduce the latency of a connection for an application designed to check database availability for receiving requests. I would like to share with you my experience, along with other articles that will be published shortly.
