Connection pooling and Login timeout expired [SQLSTATE HYT00]

Question

Post reply

Connection pooling and Login timeout expired [SQLSTATE HYT00]

ravisamigo

SSCertifiable

Points: 7440
More actions
December 6, 2010 at 1:49 am

#243601

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.

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

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

ravisamigo SSCertifiable Points: 7440 More actions · Answer 1

Dear All,

Kindly advise me on this.

Thanks and Regards,

Ravichandra.

SQLBuddy123 One Orange Chip Points: 28294 More actions · Answer 2

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 More actions · Answer 3

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 More actions · Answer 4

Can someone help me out please...

Thanks and Regards,

Ravichandra.

Feeg SSCrazy Points: 2766 More actions · Answer 5

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 More actions · Answer 6

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 More actions · Answer 7

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

tomas.nelson Old Hand Points: 313 More actions · Answer 8

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