Saturday, 21 September 2013

Troubleshooting timeout expired errors

Having .NET Applications working on SQL Server, some timeout errors can be raised unexpectedly for two possible causes: bad-written SQL code (specially Ad-Hoc queries) and application issues. Under this circumstance, developers will always blame SQL Server at first glance because they do think it is a timeout misconfiguration in SQL Server. In my experience troubleshooting these issues, I would say that lots of them are not mostly related to SQL Server configuration itself. Furthermore, when it comes to application as the root cause, reading the SQL Server error log, you will not find any error about timeout or login failed events. What does it mean? the application never tried to connect to SQL Server because it was still working at application level when the error was raised. Thus, timeout error is inside the application at Net SqlClient Data Provider level (see the CommandTimeout property value configured for the application connection). This 'CommandTimeout' property specifies the number of seconds that a application provider should wait for result sets before sending a timeout signal. It is well-known that the default is 30 seconds, therefore, it may not be enough due to application performance problems, indeed.
Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.
Timeout expired. 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.
In order to get it fixed, firstly, developers have to check .NET code within applications. In some cases, the developers I worked with, did find infinite loops or slow codes which spent much time before sending SQL Queries to the database engine, as a result, the timeout threshold was reached at application level and, obviously, the error was raised.

By the way, the following error could also be related to timeout:
Unable to connect to SQL Server session database.
Having checked the application, you can increase the 'CommandTimeout' property value to 60 seconds (if it is not enough then change to 120 or 180, and also considerate using 0 (unlimited) which should be a workaround while the problem is being traced and fixed). That is all for now, let me know any remark you may have. Thanks for reading!