How to Troubleshoot Timeout on Web Requests, Connection Pool Errors, Data Locking, etc

Question

I have a question, and am hoping someone here can help. I am running a .NET web application, and am receiving an error under certain circumstances. The error is "System.Data.SqlClient.SqlException: Timeout expired." Though this is being generated by C#.NET code that is connecting to a database, I do not think the problem is related to the code, which is why I am posting the question here instead of StackOverflow.com.

This timeout is only happening to some users of the application, not all. And, I have tried expensively to analyze the SQL, but can't find any performance problems with it. The SQL in question runs in just a few seconds for me, whereas the error mentioned above is being seen after several minutes of waiting. The trouble is, this ONLY seems to be happening when accessing the site, not when running the same code "behind the scenes." This has led me to believe that the problem is somehow related to transactional blocking, which is something I know very little about.

So, my question is for those that understand transactional logic, connection pool errors, and/or the way web requests for data differ from queries run right in a DBMS (SQL Server). I really don't know anything about this sort of thing, and so am completely at a loss. I'm really hoping someone here can offer some advice or point me in the right direction.

If I have posted this question in the wrong place, please let me know.

Thank you in advance for your help.

Answer 1

in order to isolate the problem you already did that by running the sql command behind the application through an SQL environment. Anyway, there are 2 locations where the timeout can come from. First the timeout specified in application connection string, and second in SQL engine's properties (Right click SQL server in SSMS - Properties - connection window)

By default the timeout set in SQL is 600 seconds which should be enough for a web query to happen. Nevertheless, If the application timeout is set at a lower value and it is above the client's wait-time than a timeout error will be thrown.

A query times out because of a number of reasons. Here you can search for possible reasons. If this is happening randomly than you don't have to worry about ports or firewall settings or server name spelled correctly. But you should check the client's enabled protocols to match at least one of the enabled on server part (TCP/IP is the most common that should be enabled).