How can I troubleshoot timeout expired errors in Microsoft SQL Server?

Note:
You can avoid many SQL timeout expired errors by manually registering your server's Service Principal Name (SPN). To do so, email Support Center Tier 2 with the FQDN of the SQL server, the port, and the service account username.

In Microsoft SQL Server, many different factors can cause timeout expired errors. The information here may help troubleshoot, but is not guaranteed to solve all timeout errors.

On this page:


Introduction

In SQL Server, connectivity problems can cause a "Timeout Expired" message to appear in various forms and from different sources, including Microsoft SQL Server Management Studio, ADODB, ODBC, .NET, VB, and other types of scripts and applications. Errors may look like:

 2/9/2009 5:33:00 PM - Validation ODBC Connection Failed Error Number: -2147467259 Source: Microsoft OLE DB Provider for ODBC Drivers Description: [Microsoft][ODBC SQL Server Driver]Timeout expired

If you aren't able to quickly solve the errors, email Support Center Tier 2. Tier 2 can help troubleshoot, and can open a support incident with Microsoft if problems continue. UITS can verify and troubleshoot network connectivity.

Note:
Even with Microsoft's assistance, resolving these errors at IU has taken up to six months. UITS recommends contacting all available support resources early in the troubleshooting process.

Sample troubleshooting scenario

Note:
This scenario is adapted from the troubleshooting experience of the IU School of Medicine Division of Biostatistics. Greg Puetz provided the sample output, which has been changed to remove specific computer names.

When troubleshooting these errors, the Division of Biostatistics created a script to connect to an SQL Server experiencing the timeout error. They used three test scenarios: repetitive connections, shutting down SQL Server (to simulate a server outage), and deactivating the client NIC (to simulate a network outage). These are some of the resulting errors:

  • An unforced ADODB timeout:
     2/11/2009 1:30:31 PM - ADODB_Script_Running_Every_10seconds Connection Failed Error Number: -2147467259 Source: Microsoft OLE DB Provider for SQL Server Description: Timeout expired 
  • An unforced ODBC timeout:
     2/9/2009 5:33:00 PM - Validation ODBC Connection Failed Error Number: -2147467259 Source: Microsoft OLE DB Provider for ODBC Drivers Description: [Microsoft][ODBC SQL Server Driver]Timeout expired
  • SQL Server shut down during ADODB connection attempt:
     2/11/2009 2:12:51 PM - ADODB_Script_Running_Every_05seconds Connection Failed Error Number: -2147467259 Source: Microsoft OLE DB Provider for SQL Server Description: [DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access denied. 
  • Client NIC disabled during ADODB connection attempt:
     2/11/2009 2:18:37 PM - ADODB_Script_Running_Every_05seconds Connection Failed Error Number: -2147467259 Source: Microsoft OLE DB Provider for SQL Server Description: [DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access denied.

With the help of Microsoft and UITS, the Division of Biostatistics was eventually able to resolve the errors. Per Microsoft's suggestion, UITS was able to turn off RSS (Receive Side Scaling) on the NICs of the domain controllers during a maintenance window. In this case, the change resolved the errors.

Further troubleshooting steps

Other users at IU continue to see similar timeout errors. In these cases, the network itself (routers, switches, wiring) has not been the identifiable cause. Several questions and tests can help further troubleshooting:

  • Determine whether the problem is caused by a network issue (a router, switch, or wiring problem) by checking if:
    • Other applications or services (e.g., printing) are experiencing network issues on the same computer or server.
    • There are problems with Internet connectivity.
    • When pinging the server, there is packet loss or high latency.
  • If you use NetBIOS or DNS names to refer to SQL Server, try using IP addresses instead. Ensure that computer names resolve both forward and in reverse. For example, from a Windows command prompt, try nslookup 10.4.3.2 and nslookup test.box.iu.edu to verify bidirectional resolution via DNS.
  • In a custom script, two timeouts can be configured. One timeout will wait for a response during connection and login, and the other will wait for a response from an executed command. For more, see A DBA's Quick Guide to Timeouts by Chris Kempster.
  • To determine if the errors are related to ADS, use a script simulating logins every 30 to 60 seconds. Logins to SQL Server using ADS can be compared with SQL authentication and local Windows accounts.
  • Persistent pings to the server from an affected computer can determine if network connectivity is lost at any point during the day. No packets should be lost, and the latency should usually be less than 1 ms. If the computer is busy with other tasks during this process, it may affect the results by occasionally dropping packets and raising latency.
  • Monitor CPU usage of affected computers throughout the day in order to find a possible correlation to failed SQL Server logins.
  • UITS can monitor switchports attached to the servers, and in some cases monitor the switchports of workstations or other servers attempting to connect to SQL Server. This can help verify that there are no errors in the switchports or their configurations (e.g., dropped packets, collisions, duplex/speed mismatches).

    For network troubleshooting and verification assistance, email Campus Network Engineering. Include the IP and MAC addresses of the server, and of the device initiating the connection.

More information

The resources below provide background on the timeout error and suggest possible solutions, which could help further troubleshooting and investigation.

This is document aytk in the Knowledge Base.
Last modified on 2018-01-10 14:29:13.

  • Fill out this form to submit your issue to the UITS Support Center.
  • Please note that you must be affiliated with Indiana University to receive support.
  • All fields are required.

Please provide your IU email address. If you currently have a problem receiving email at your IU account, enter an alternate email address.

  • Fill out this form to submit your comment to the IU Knowledge Base.
  • If you are affiliated with Indiana University and need help with a computing problem, please use the I need help with a computing problem section above, or contact your campus Support Center.

Please provide your IU email address. If you currently have a problem receiving email at your IU account, enter an alternate email address.