First published on MSDN on Jan 27, 2019
Very frequently problems connecting to SQL Server originate from network-related or authentication issues. Here are examples of connectivity timeout errors:
- Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.
- System.Data.SqlClient.SqlException (0x80131904): Connection Timeout Expired. The timeout period elapsed while attempting to consume the pre-login handshake acknowledgement. This could be because the pre-login handshake failed or the server was unable to respond back in time. The duration spent while attempting to connect to this server was - [Pre-Login] initialization=23; handshake=14979; ---> System.ComponentModel.Win32Exception (0x80004005): The wait operation timed out
- System.Data.SqlClient.SqlException (0x80131904): Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding. ---> System.ComponentModel.Win32Exception (0x80004005): The wait operation timed out
- Connection Timeout Expired. The timeout period elapsed while attempting to consume the pre-login handshake acknowledgement. This could be because the pre-login handshake failed or the server was unable to respond back in time. The duration spent while attempting to connect to this server was - [Pre-Login] initialization=21036; handshake=0; (Microsoft SQL Server, Error: -2)
Here are Common Culprits:
- Network Connection Issues
For more information, see this excellent SQL connectivity Troubleshooter
SQL Server Contributors to Connectivity Problems
There is small number of issues on SQL Server Database engine side that can lead to connectivity problems. Here is the list. This is to help with what to look for when a connectivity problem is raised. But as a general practice start with network problems
1. Scheduler problems (non-yielding, deadlocked, etc) - check Errorlogs
2. Running out of worker threads - check Perfmon for SQLServer:Wait Statistics->Wait for the worker (avg wait time, waits in progress), THREADPOOL wait type in DMVs (wait durations say over 10 seconds). Typical causes for running out of worker threads are either long waits (blocking, latch waits, I/O waits, huger parallel queries), or a very large number of long-running queries.
3. Sp_reset_connection delays - much more rare - check sp_reset_conneciton times in a trace
4. Database unavailable (including AG is not online or resolving)
5. Out of Memory - low virtual memory on machine or processes are being paged out. Check Perfmon counters (Memory->Available MBytes) for RAM exhaustion and thus low virtual memory. For Processes being paged out, look at Perfmon (Process->Working Set) and see if there is a sudden drop of most/all processes' working sets on the system.
Namaste
Joseph
