1

I'm trying to narrow down and debug quite elusive error:

State: 08001
Code: 258
Msg: [Microsoft][SQL Server Native Client 10.0]TCP Provider: Timeout error [258]. 

State: HYT00
Code: 0
Msg: [Microsoft][SQL Server Native Client 10.0]Login timeout expired

State: 08001
Code: 258
Msg: [Microsoft][SQL Server Native Client 10.0]Unable to complete login process due to delay in prelogin response

What I have tryied so far:

  • making sure that max RAM setting is correct and smaller by 2GB then physical size of RAM
  • making sure that work threads are unlimited
  • making sure that remote connections limit is few times bigger then measured load during "peak hours"
  • making sure that remote connections login time out is set to 20 seconds
  • making sure that connection between PHP server and DB server is stable (traceroute every 1 minute returned max connection time of 23ms)
  • making sure that DNS is not a problem (PHP use IP of DB for connection)
  • making sure that app have unique login credentials not shared with any other app
  • making sure that TCP/IP ports where not exhausted
  • making sure that app login is SQL login to exclude any issues with AD
  • making sure that connection pooling is on
  • making sure that query timeout is set to very high number of seconds

Non of the above actions so far produced any issue with DB/network configuration nor is there any apparent problem with application code.

Most of the time connection creation succeed.

Q: What other aspects can have impact on the connection creation? And produce such seemingly random failures?

przemo_li
  • 61
  • 4

1 Answers1

3

Check if the issue exists on the localhost of the server to eliminate the possibility of it being network hardware causing the timeout. Assuming the issue goes away when you test on the localhost, then it may very well be network related.

You mention a 23 ms latency when checking a traceroute every 1 minute. 23 ms doesnt seem like a LAN connection and most likely has multiple hops where you may also be seeing the occasional packet loss.

Instead of doing a traceroute every 1 minute you should do a ping with no timeout like ping -t servername and let it sit. Then after a while you can check the statistics with CTRL + BREAK and look for packet loss and or fluctuations in the latency.

Another option (unless you are already doing this) is to use PDO to connect

Community
  • 1
Tim Penner
  • 1,889
  • 14
  • 22
  • You mean that I should have run app code on DB server that connect periodically with same credentials? – przemo_li Mar 17 '16 at 12:34
  • 23nm was extreme. Most where <1nm. – przemo_li Mar 17 '16 at 12:35
  • Test, isolate, troubleshoot, narrow down. Yes i am suggesting that you test the application code on the database server to see if the problem persists. Have you looked at the resource monitor of the server to see if it seems bogged down at the times when this issue is happening? – Tim Penner Mar 17 '16 at 17:17
  • Small update. App used 'server_name' and not IP when errors occured. Changed that and errors never reoccurred for my app nor for second app that had same issues. But did not correlated those two as app usage is sporadic. So that could have been it. – przemo_li Mar 31 '16 at 08:05