SQL Server becomes unresponsive with an error message

Question

Post reply

SQL Server becomes unresponsive with an error message

pmohan

SSC Eights!

Points: 941
More actions
March 31, 2009 at 11:41 am

#130366

We are using SQL Server 2005 SP2 (9.00.3257.00). At times it becomes unresponsive. The even log is showing the following error messages.
"The client was unable to reuse a session with SPID 86, which had been reset for connection pooling. This error may have been caused by an earlier operation failing. " ,
"There is insufficient system memory to run this query.".
Does anyone have any idea about this?

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

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

sebastien piche Ten Centuries Points: 1010 More actions · Answer 1

look this kb

http://support.microsoft.com/kb/937745/en-us

a tink is verry similar

pmohan SSC Eights! Points: 941 More actions · Answer 2

I did put in SP3 yesterday into the SQL Server box. It is still happening, but this time there is no error in Event Log, but the error is in SQL Server Error Logs, the error is

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

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

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

When this happened I was unable to get into SQL Server using SMO also.

Does any one know why this is happening?

Also is there any way I can monitor how much memory each session in SQL Server uses?

sebastien piche Ten Centuries Points: 1010 More actions · Answer 3

look this KB

http://support.microsoft.com/kb/934751/en-us

pmohan SSC Eights! Points: 941 More actions · Answer 4

I looked into the article. We don't have so many sql users in the system. Also I'm not just getting this error using SMO. This error is happening for applications also.When this happens no apps/reports/users can be able to access the database.

Anyways thanks for the kb.

sebastien piche Ten Centuries Points: 1010 More actions · Answer 5

sebastien piche

Ten Centuries

Points: 1010

April 1, 2009 at 12:04 pm

#970132

maybe call microsoft support.

OomBoom Hall of Fame Points: 3481 More actions · Answer 6

I have the same problem. In my case it is not a SQL problem but a script that is run and not commited. One of our processes runs the script but because of bad coding inside the transaction it fails and the transaction is not commited. This causes a ripple effect,by locking more and more systems, until the SQL server generates the following error "All schedulers on Node 0 appear deadlocked due to a large number of worker threads waiting on CXPACKET. Process Utilization 0%." and then switches the Service broker off if the problem is not sorted out.

>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
Do not reinvent the wheel.
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>

sql_lock SSCrazy Eights Points: 9373 More actions · Answer 7

Look at the thread below

http://www.sqlservercentral.com/Forums/Topic585961-360-1.aspx

AmitK.Hasija SSC Journeyman Points: 75 More actions · Answer 8

Hi Journeyman,

We are facing the same problem that you had faced. SQL server becomes unresponsive at high concurrent connections. We have SP3? Were you able to find solution to the issue.

Thanks a lot for your help

Regards

Amit

pmohan (4/1/2009)
I looked into the article. We don't have so many sql users in the system. Also I'm not just getting this error using SMO. This error is happening for applications also.When this happens no apps/reports/users can be able to access the database.
Anyways thanks for the kb.