none
Connection Timeout Expired. In Sql Server 2005

    Question

  • Hi All,

    We are facing Connection time out intermittently in our application (database is sql server 200). Below is the error

    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.  
    

    In some case we are experiencing Time out (this is acceptable due to command time out ), but around 70 percent cases we are facing this issue.

    Please let me know how should i fix this issue. 

    Thanks in advance

    Wednesday, January 18, 2017 12:30 PM

All replies

  • Hello,

    pre-login handshake timeout often occurs when the (SQL-)Server is to busy to resonse. Check the CPU utilization of the Windows & SQL Server process on times, when this timeout occured.


    Olaf Helper

    [ Blog] [ Xing] [ MVP]


    Wednesday, January 18, 2017 12:34 PM
  • Hi,

    Thanks! for the above details. One quick query, i've captured the time during which the connection time out error happened and accordingly will check the CPU utilization of windows at that point of time.

    For Sql Server process , i'm not clear could please elaborate on that and where can i get those details ( is it from the monitoring tool, in our environment we  use Redgate monitoring tool to capture all the important metrics of prod server)

    Wednesday, January 18, 2017 12:41 PM
  • If you have a Monitoring Software then you can look up CPU utilization history there. SQL Server logs only the last 256 average values per minute, you can query it with this script: Utilization History

    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Wednesday, January 18, 2017 1:35 PM

  • Hi,

    When should  i use the above script Utilization History, for instance in my application log the time out error is logged in below time

    Connection Timeout Expired 1/17/17 2:17:54.621 AM
    Connection Timeout Expired 1/17/17 12:12:56.889 AM

    And from the result-set should i check for SystemIdle & ProcessUtil for any issue?

    To give more background of how we are getting the error:

    We have some stored procedures that are executed in parallel (same sp is used with different parameters)  through multiple threads from the application and with this some execution gets completed and some gives us connection time out failure and some goes for Timed out failure.

    Thanks


    Wednesday, January 18, 2017 2:19 PM
  • A I wrote, SQL Server logs one average performance counter per minute and max 256 values at all => max. value of the last 4 hours 16 min

    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Wednesday, January 18, 2017 5:32 PM
  • Hi,

    Today we have same error 'Connection Time out' logged in the application and i also captured the data utilization script shared earlier (Please see below). The error occurred around 12:26 AM , but there is not much utilization for SQL CPU at that point of time. Also the CPU utilization of the server was below 20% (Detail img below)

    Please let me know if i'm missing some thing here. Is there any other area that i need to pay attention.

    Please advice.

    Thanks!Result

    ====================== CPU Util Report  ========================

    Report

    • Edited by MS_Novice Thursday, January 19, 2017 1:13 PM Added one new Image
    Thursday, January 19, 2017 1:09 PM