Connection pooling and Login timeout expired [SQLSTATE HYT00]

  • Dear All,

    Can some one advise on the below please.

    Iam facing these errors daily in logs.

    ERROR 1

    -------------

    [298] SQLServer Error: 258, Shared Memory Provider: Timeout error [258]. [SQLSTATE 08001]

    [165] ODBC Error: 0, Login timeout expired [SQLSTATE HYT00]

    [298] SQLServer Error: 258, Unable to complete login process due to delay in prelogin response [SQLSTATE 08001]

    [382] Logon to server '(local)' failed (ConnUpdateStartExecutionDate)

    ERROR 2

    --------

    Error: 18056, Severity: 20, State: 27.The client was unable to reuse a session with SPID 187, which had been reset for connection pooling. This error may have been caused by an earlier operation failing. Check the error logs for failed operations immediately before this error message.

    Our Environment

    -----------------

    SQLServer2005 -- Standard Edition X86 platform

    select serverproperty('productlevel') ---> SP3

    select serverproperty('productversion') --->9.00.4053.00

    Your quick response is much appreciated.

    Thanks and Regards,

    Ravichandra.

  • Dear All,

    Kindly advise me on this.

    Thanks and Regards,

    Ravichandra.

  • Hi Ravi,

    This could be due to worker threads not available to process the incoming login or the server might be experiencing some CPU pressures. Also this could be due the improper config of the connection pooling.

    Please check these links..

    http://sqlserver-qa.net/blogs/x64/archive/2009/07/14/5811.aspx

    http://support.microsoft.com/kb/937745

    http://connect.microsoft.com/SQL/feedback/ViewFeedback.aspx?FeedbackID=468478

    Thank You,

    Best Regards

    SQLBuddy.

  • Thanks for your reply.

    I think, this could be due to worker threads not available to process the incoming login.

    select max_workers_count from sys.dm_os_sys_info -- 288 [ it has been set to 0( Zero) by default in SQLServer2005]

    The server might be experiencing some CPU pressures - > there is no CPU pressure

    Improper config of the connection pooling - > the connection pooling setting is fine

    http://support.microsoft.com/kb/937745

    -----------------------------------------

    I think, this fix is not worked since we have SP3(SQLServer2005-SP3,version - 9.00.4053.00 )

    Our Environment

    --------------

    SQLServer2005 -- Standard Edition X86 platform

    select serverproperty('productlevel') - SP3

    select serverproperty('productversion') - 9.00.4053.00

    Min Memory -10240 MB

    Max Memory -30720 MB

    No.of Processors-8

    Memory - 32 GB

    Kindly assist me on this issue.

    Thanks and Regards,

    Ravichandra.

  • Can someone help me out please...

    Thanks and Regards,

    Ravichandra.

  • what switches did you change in the boot.ini file?

    3GB and PAE??

    I'm asking because of your max and min memory settings above ...

    MCITP: Database Administrator 2005
    MCTS SQL Server 2008
    MCP SQL 2012/2014
    MCSA SQL Server 2012/2014
    MCSE Data Management and Analytics

  • Default value for the Worker threads has been changed to 0 (Dynamic) for SQL Server 2005 and 2008. But it can be set to 288 for your system under special circumstances but needs testing before hand.

    What is the query timeout set for the application . Try to increase it and check it solves the issue.

    Are you having High Processor Interrupts and Context switches per sec.

    Are you experiencing any kind of memory or IO pressures ? Run a basic Perfmon tace and check for any performance issues causing timeouts.

    Also check for any Index Framentation, Missing Indexes, Outdated statistics.

    What is the Page file size on the server ?

    Thank You,

    Best Regards,

    SQLBuddy.

  • Did you ever find out what the problem was? We are experiencing the same thing.

  • I have the same problem, does anyone have a definitive solution?

Viewing 9 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply