Connection pooling and Login timeout expired [SQLSTATE HYT00]

  • ravisamigo

    SSCertifiable

    Points: 7440

    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.

  • ravisamigo

    SSCertifiable

    Points: 7440

    Dear All,

    Kindly advise me on this.

    Thanks and Regards,

    Ravichandra.

  • SQLBuddy123

    One Orange Chip

    Points: 28294

    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.

  • ravisamigo

    SSCertifiable

    Points: 7440

    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.

  • ravisamigo

    SSCertifiable

    Points: 7440

    Can someone help me out please...

    Thanks and Regards,

    Ravichandra.

  • Feeg

    SSCrazy

    Points: 2766

    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

  • SQLBuddy123

    One Orange Chip

    Points: 28294

    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.

  • Patti Johnson

    SSCertifiable

    Points: 7054

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

  • tomas.nelson

    Old Hand

    Points: 313

    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