Hello all,
Mar 10, 2014 - The connection could have been closed by the MySQL server because. The default 8 hours ( waittimeout = 28800 ) works well with properly.
While working with SQL Server, one of the common issue application teams report is timeouts. In this article, we are covering connection and Command timeouts and ways to isolate them.
There are 2 types of timeouts.
- Connection timeout
- Command Timeout
CONNECTION TIMEOUT:
It is the time in seconds application waits while trying to create a connection with SQL Server before terminating the attempt. Default value of connection timeout is 15 seconds.
When you encounter Connection timeout issues, you should review:
- Check if you are able to telnet SQL Server on SQL port
- Check if the 3 way TCP handshake is working
- The troubleshooting approach should be on fixing SQL Connectivity with application.
Additional information on troubleshooting SQL Connectivity issues is documented HERE.
Reviewing the exception and the stack trace is a starting point to isolate connection timeout issues. Reviewing the thread stack trace, you would observe that application tries to create a connection and times out post encountering Connection timeout value (by default 15 seconds).
![Server Server](/uploads/1/2/5/8/125874928/469925895.jpg)
Stack Trace:
COMMAND TIMEOUT:
This is the time in seconds to wait for the command to execute. This setting allows the cancellation of an ExecuteReader method call, due to delays from network traffic or heavy server use. The default value is 30 seconds.
When you encounter Command timeout issues, you should review:
- Check if there are any performance issues with SQL Server like blocking
- Check in SQL Server why the query execution is taking more than Command Timeout value.
- Collect Attention events in SQL profiler/Extended Events and track all queries timing out and review the waits.
Additional details on troubleshooting SQL query/command timeout is documented HERE:
Reviewing the stack trace closely, you would observe that application connects to SQL Server and it calls ExecuteReader method for retrieving data from database.
Exception Details: System.ComponentModel.Win32Exception: The wait operation timed out
Source Error:
An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below. |
Stack Trace:
SUMMARY:
To summarize, when an application reports timeout error, its essential to identify the type of timeout, if it is Connection/Command timeout. Exception reported and the stack trace is a starting point to isolate the type of timeout. Accordingly, further data collection steps and actions can be performed to isolate the exact issue.
Hope this blog helps in identifying the timeouts and helps in isolating the timeout issues.
Please share your feedback, questions and/or suggestions.
Thanks,
Don Castelino | Premier Field Engineer | Microsoft
Don Castelino | Premier Field Engineer | Microsoft
Disclaimer: All posts are provided AS IS with no warranties and confer no rights. Additionally, views expressed here are my own and not those of my employer, Microsoft.