0

I have a .Net Application(REST API) which accesses SQL Server Database. Sometimes When there are many API requests at the same time, I get Connection Timeout exception due to which all the request fails. This is not always the case and happens only when there are many requests hitting the server at the same time.

Exception Details:

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=4; handshake=14992; ---> System.ComponentModel.Win32Exception (0x80004005): The wait operation timed out.

Any help here will be highly appreciated.

Tasos K.
  • 7,979
  • 7
  • 39
  • 63
Sam
  • 1
  • 5
  • 1
    You might want to consider the way you are connecting to SQL Server. Could you provide more information on how you connect to the database? – Tasos K. Jan 31 '19 at 08:26
  • 2
    in this case may be few reasone. 1. You are using express sql server. 2. Dead lock between your queries. 3. Queries executed more then standart timeout- 30 sec – KreminT Jan 31 '19 at 08:34
  • https://learn.microsoft.com/en-us/dotnet/framework/data/adonet/sql/enabling-multiple-active-result-sets might be useful. Also profile your SQL server requests - might occur multiple selects on non indexed column data e.t.c. if you write data - then various locks initiated .... – Vladimir Jan 31 '19 at 11:06
  • 1
    >>>the pre-login handshake failed<<< This means that the connection was not established at all. It's not runaway query/blocking/deadlocking, the query submitted just have no chance to be executed as the CONNECTION ITSELF FAILED – sepupic Jan 31 '19 at 12:37

1 Answers1

0

From this (upvoted) comment

in this case may be few reasons:
1. You are using SQL Server Express
2. Dead lock between your queries
3. Queries executed more then standard timeout- 30 sec

You should first exclude N1, as the server Edition has nothing to do with connection timeout that is imposted by client

Then exclude N2 as the deadlocks are resolved automatically by server deadlock monitor that controls for deadlocks every 5 seconds or even less when the deadlocks are frequent.

Than exclude N3 as connection timeout has nothing to do with execution timeout. And your timeout is CONNECTION timeout.

You should increase connection timeout on your client or troubleshoot your server, what happens there that your server becomes unresponsive.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
sepupic
  • 8,409
  • 1
  • 9
  • 20
  • Sorry, but i don't agree with your answer. Express edition has limits, which can affect performance. Next, deadlock. User can lock one or more tables. Sorry, but, we didn't seen any queries or tables and we can write only supposition. And last, the result of the previous reasons: slowly requests execution (more than standard connection timeout -30 sec), and connection has closed without receiving an answer – KreminT Feb 01 '19 at 09:37
  • 1
    >>>connection has closed without receiving an answer<<< means Execution timeout. But the connection that was not established at all is CONNECTION timeout. The error says clearly "**Connection** Timeout Expired" This means that your query did not ever start. You just was not connected to server at all. For example, if the server is down, or your connection string is wrong, you also has CONNECTION timeout. – sepupic Feb 01 '19 at 10:17
  • Ok. It's true. Thanx. Sam has wrote " When there are many API requests at the same time, I get Connection Timeout exception". I wrote only supposition. – KreminT Feb 01 '19 at 10:43
  • Maybe answer is here https://stackoverflow.com/questions/15488922/connection-to-sql-server-works-sometimes – KreminT Feb 01 '19 at 10:44
  • The second answer from the lik you posted suggests the same as my answer: "to add a connection timeout of 30 seconds into the connection string". As the first attempt connection timeout should be increased – sepupic Feb 01 '19 at 10:48
  • Default connection timeout 15 sec. It's a lot. More than 15 seconds wait for answer? I think must be better solution. We doesn't live in the 20th century)))))) – KreminT Feb 01 '19 at 10:57
  • Following you it seems that 24 persons who upvoted that answers were dinosaurs – sepupic Feb 01 '19 at 12:04
  • No. It's fix this problem but create new with performance. I said that solution must be more complex. And need to search reason of such issue. – KreminT Feb 01 '19 at 12:10
  • ...And the cause may be even the network problems, but they are surely NOT in your list – sepupic Feb 01 '19 at 12:12
  • Okey. Yes, maybe, i wrote not perfect comment, but it wasn't "answer" and i wrote only supposition. And we both don't know what is the real reason of this issue. I think this discussion are finished. Your answer is more look like as correct – KreminT Feb 01 '19 at 12:34
  • @KreminT I just wanted to emphasize that the problem is in CONNECTION timeout, not in an EXECUTION timeout, and your comment has a list of possible cause of EXECUTION timeout. The CONNECTION timeout can be caused by network problems or wrong connectivity settings, it's something that not permits you to COnNECT. It can be even firewall, but it's not the case when "This is not always the case and happens only when there are many requests" – sepupic Feb 01 '19 at 12:41
  • Please, read Sam's question. I have read " Sometimes When there are many API requests at the same time, I get Connection Timeout exception due to which all the request fails." And for my answer please read it https://social.msdn.microsoft.com/Forums/sqlserver/en-US/9aa57b92-9c51-41ef-b30a-ce3c971dea31/sql-express-timeout?forum=sqlexpress – KreminT Feb 01 '19 at 12:52